Forum Discussion

Winterkid's avatar
Winterkid
Copper Contributor
Oct 10, 2022

Sharepoint Online Calculated column

Hi All,
I am currently trying to use calculated columns whereby to do calucaltion.
I want the calculated Column TotalAmountSGD, to calculated IF the Currency is SGD then Calculate the Cost of the, Else Currency is Not SGD then Calculate ConvertedSGDRate.

can i know what is the correct formula should i put

CurrencyCostConvertedSGDRateTotalAmountSGD(Calculated Column)

SGD

100 

100

SGD200 200
YEN18000176.4176.4
USD5071.7871.78
YEN800079.0179.01
SGD50  50
MYR1002525
USD1501545.711545.71
  • RobElliott's avatar
    RobElliott
    Silver Contributor

    Winterkid you've not said where you're putting the rate of exchange in to do the calculation, because in your image you see to have already done the calculation. Is it a lookup to another list that holds the daily exchange rate for each currency? One way you can make life easy for yourself if you just do a calculated column with cost*rate and if the currency is SGD then the rate is 1 as in my example image below.

     

    Rob
    Los Gallardos
    Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)

    • Winterkid's avatar
      Winterkid
      Copper Contributor

      RobElliott thank you for your fast reply.
      the exchange rate will be key in from powerapp application itself from my finance members.
      as our currency default is SGD my finance colleague will not be going into the application to manually key 1 into the exchange rate as it serve no purpose for them by doing so.

       

      If i set the default value 1 to the exchange rate then it will appear and calculate all the value which will provide false data to the employee before finance colleague update the exchange rate.

       

Resources