Forum Discussion

sanai1505's avatar
sanai1505
Regular Visitor
4 years ago
Solved

How to convert currency?

Hi Everyone,

 

I'm new to Power BI and I'm having some trouble with changing the currency. Some of my data is in USD and some in CAD, I need to convert all the rental costs into USD.

 

 

punit

Need Help
Tips and Tricks
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    Hi sanai1505 

    Thanks for reaching out to us.

    When you have multiple currencies in your sales table, it is common practice to create a currency exchange rate table. Then perform currency conversion in the sales table according to the exchange rate in the rates table.

    For example, you can create a custom USD column in Power Query Editor,

    (let cur = [currency] in Table.SelectRows(rate, each [currency] = cur)){0}[rate] * [sale]

    Or you can create a column with DAX,

    DAX Column = 
var _rate= CALCULATE(MAX('rate'[rate]),FILTER(ALL('rate'),'rate'[currency]= EARLIER('Table'[currency])))
return _rate*'Table'[sale]

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • VahidDM's avatar
    VahidDM
    Icon for Super User rankSuper User
    4 years ago

    Hi sanai1505 

     

    In Power BI desktop, open the Model tab, and check the columns type same as below:

     

     

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

    Appreciate your Kudos!! 

    LinkedIn | Twitter | Blog | YouTube 

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

    Hi sanai1505 

    Thanks for reaching out to us.

    When you have multiple currencies in your sales table, it is common practice to create a currency exchange rate table. Then perform currency conversion in the sales table according to the exchange rate in the rates table.

    For example, you can create a custom USD column in Power Query Editor,

    (let cur = [currency] in Table.SelectRows(rate, each [currency] = cur)){0}[rate] * [sale]

    Or you can create a column with DAX,

    DAX Column = 
var _rate= CALCULATE(MAX('rate'[rate]),FILTER(ALL('rate'),'rate'[currency]= EARLIER('Table'[currency])))
return _rate*'Table'[sale]

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.