Forum Discussion

stibra's avatar
stibra
Frequent Visitor
7 years ago
Solved

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

Need Help
  • Thim's avatar
    Thim
    7 years ago

    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]
     

3 Replies

  • Thim's avatar
    Thim
    Resolver V
    7 years ago

    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]
     
  • Omega's avatar
    Omega
    Impactful Individual
    7 years ago

    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 :)