Forum Discussion

ferreros's avatar
ferreros
Frequent Visitor
4 years ago

Create a measure for currency

Hello,    I have a table with currency. I have another table (budget) where amout is  set in customer currency   so i want to create a column in the Budget table thta calculate amoun...
Need Help
v-jingzhang's avatar
v-jingzhang
Icon for Community Support rankCommunity Support
4 years ago

Hi ferreros 

 

You can first create a column in Budget table which converts all amounts into USD.

Amount in USD = 
VAR vRate =
    MAXX (
        FILTER ( 'Exchange Rate', 'Exchange Rate'[IsoCode] = Budget[CurrencyIsoCode] ),
        'Exchange Rate'[ConversionRate]
    )
RETURN
    DIVIDE ( Budget[Amount], vRate )

 

Then create a measure to convert USD amount into other currencies. This measure is dynamic. If you don't pick any currency code, it will return USD amount by default. 

Measure = SUM(Budget[Amount in USD]) * SELECTEDVALUE('Exchange Rate'[ConversionRate],1)

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

ferreros's avatar
ferreros
Frequent Visitor
4 years ago

Hello v-jingzhang 

 

this works almost as expected.

 

For USD it give the value but nothing happen when I selecfte another currency, in this example Customer is a USD customer.

 

 

I fact i tried with other cutomers in different currency, and mnumbers only shows if i select nothing or the customer currency only

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi ferreros 

     

    Which table does the IsoCode column in the slicer come from? As you use SELECTEDVALUE('Currency Type'[ConversionRate]) in the measure, the IsoCode should also come from 'Currency Type' table. I guess you were using another table so SELECTEDVALUE() function couldn't get a proper value and used the default value instead. 

     

    Regards,

    Jing

    • ferreros's avatar
      ferreros
      Frequent Visitor
      4 years ago

      Hello v-jingzhang,

      the slicer come from currency type

       

      • v-jingzhang's avatar
        v-jingzhang
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi ferreros 

         

        Can you split the measure into two measures to return SUM(Budget[Amount in USD]) and SELECTEDVALUE('Exchange Rate'[ConversionRate],1) separately? I'd like to know which one doesn't return the expected result. Is there any relationship between tables? If possible, can you share the pbix after removing sensitive info? 

         

        Jing