Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

SUM different currencies

Hello!

 

How do I get the total sum of a table with values, when which value is in a diferent currency?

 

Values

 

 

 

 

 

 

 

 

 

 

 

In Excel, I would use a PROCV to solve this problem. But I have no idea how I would do it on Power BI.

 

 

 

 

 

 

 

 

 

 

 

 

Any suggestion?

Need Help

6 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hey MFelix thanks for the help

       

      I tried this site's method but TotalSales it's not working.

      No idea what I'm doing wrong.

       

      Could you look at my file?

       

      Exemplo

       

      Best Regards

       

       

      -----EDIT-----

       

      Probably not the best way (I started learning DAX two weeks ago), but I was able to do what I needed.

       

      I used:

      Total_Orçado_ARS = CALCULATE(SUMX(FILTER(Arg_Orcado;Arg_Orcado[Currency]="ARS");Arg_Orcado[ValorOrçado]))

      To get only the values in the ARS currency

       

      And:

      Convert_ARS_USD = CALCULATE(SUMX(FILTER(db_Currency;db_Currency[LocalCurrency]="ARS");db_Currency[Factor]))

      To convert ARS to USD

       

      And finally:

      Total_ARS_USD = [Total_Orçado_ARS]*[Convert_ARS_USD]

      To get the total amount corrected.

       

      Basically, I'll do this for each currency. I imagine there's a more optimized way, but I need to study more.

       

      But I do accept suggestions on how to improve these formulas.

      • MFelix's avatar
        MFelix
        Icon for Super User rankSuper User
        7 years ago

        Hi Anonymous ,

         

        I was looking at your data and notice that on the Arg_Orcado you only have 3 currencys ARS, CLP, UYU is this correct?

         

        If you also look at the information you have on the exchange rate table I saw that you have from USD to other currency but not from other currencys so you only get result when you select the reporting currency USD.

         

        Can you please explain what are the several conversion factors you want to have?

         

        Regards,

        MFelix

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi,

    You will have to create and maintain a currency exchange table.

    Then to convert all values to the same currency, and after that sum them up.

     

    Look at this nice video

    https://www.youtube.com/watch?v=4dosxfNxR6M

     

    Good Luck!