Currency calculation

[stibra]

Hi,

I am looking for a formula to convert values with corresponding currency into a new column which shows all values in NOK. See example below:

Value1  Currency   ValueNOK

100       USD          796

200       EUR           2000

300       NOK          300

Following formula ValueNOK = IF('Table1'[Currency]= "USD"; 7,96*'Table1'[Value1];0) works fine for converting rows with USD, but how do I also include EUR and NOK?

br

Stian

[Thim · Accepted solution]

One way is just to keep on stacking if formulas for each currency code you have.

Example.

ValueNOK = IF('Table'[Currency]="USD";'Table'[Value1]*7,96;

IF('Table'[Currency]="EUR";'Table'[Value1]*10;

IF('Table'[Currency]="NOK";'Table'[Value1];0)

)

)

But this quickly gets messy, and can be difficult to update the currency.

I would instead make a new table and add currency codes and their values. Just click enter data, and start typing.

 CurrencyValue

USD	7,96
EUR	10
NOK	1

Then you can use lookupvalue to find the correct currency.

Something like this.

Value NOK = LOOKUPVALUE('Currency Table'[Value];'Currency Table'[Currency];'Table'[Currency])*'Table'[Value1]

[stibra]

Thanks ! This worked perfectly !

[Omega]

You can use Switch: 

ValueNOK = Swtich (true (),  Currency = "USD"; 7,96*'Table1'[Value1];
                                               Currency = "EUR"; *Table [Value1];
                                               Currency = "NOK"; *Table [Value1];0)

Next to each currency put the number that you want to multiply with value1 🙂