Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
stibra
Frequent Visitor

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

1 ACCEPTED SOLUTION
Thim
Resolver V
Resolver V

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

3 REPLIES 3
Thim
Resolver V
Resolver V

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]
 
stibra
Frequent Visitor

Thanks ! This worked perfectly !

Omega
Impactful Individual
Impactful Individual

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 🙂 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.