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 amount in CAD for example based on customer currency and exchange rate table.

 

I can't figure how to do this.

Need Help

10 Replies

    • ferreros's avatar
      ferreros
      Frequent Visitor
      4 years ago

      ok so it is not working as expected , because it brings the max value from currency table and it is not the CAD one

  • ferreros's avatar
    ferreros
    Frequent Visitor
    4 years ago

    Om i will try that to add the value in the budget table, but then i need to create a measure to calculate ammount only if currency of customer is not CAD

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

    Hi ferreros 

     

    Do you mean you want to convert all amount values into CAD ones from their original currency code displayed in CurrencyIsoCode? If so, we need conversion rate between all other currencies and CAD. But from the currency table, I only see IsoCode column and ConversionRate column, so I'm not sure these conversion rates are between which two currencies?

     

    In addition, this blog may be helpful: Currency conversion in Power BI reports - SQLBI

     

    Best Regards,
    Community Support Team _ Jing

    • ferreros's avatar
      ferreros
      Frequent Visitor
      4 years ago

      Yes that is what i want but let say to make it all in USD, as it is the main Currency actually in our system, then i will be able to convert in CAD because i have the exchange rate of it

       

      • 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.