Reply
Frequent Visitor
Posts: 10
Registered: ‎12-11-2017
Accepted Solution

Currency calculation

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


Accepted Solutions
Highlighted
Regular Visitor
Posts: 38
Registered: ‎04-13-2018

Re: Currency calculation

[ Edited ]

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
USD7,96
EUR10
NOK1
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]
 

View solution in original post


All Replies
Established Member
Posts: 155
Registered: ‎05-03-2017

Re: Currency calculation

[ Edited ]

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 Smiley Happy 

Highlighted
Regular Visitor
Posts: 38
Registered: ‎04-13-2018

Re: Currency calculation

[ Edited ]

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
USD7,96
EUR10
NOK1
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]
 
Frequent Visitor
Posts: 10
Registered: ‎12-11-2017

Re: Currency calculation

Thanks ! This worked perfectly !