SUM different currencies

[Anonymous]

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?

[Anonymous]

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!

[MFelix]

Hi @Anonymous,

Check the tutorial below on an explanation on how to achieve currency conversion.

https://www.kasperonbi.com/currency-conversion-in-dax-for-power-bi-and-ssas/

Followed this for a file I had and worked perfectly.

Regards,
MFelix

[Anonymous]

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]

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]

Hi @MFelix 

The file I sent as an example has only those 3 currencies. But I work with 7 in total (BRL, ARS, CLP, UYU, COP, ECS, PEN). And I convert the 7 to USD. I need just convert them to USD.

In my last message, I said that I had been able to do this conversion. But then I discovered that the value of the subtotal was wrong.

Digging a little more I discovered the LOOKUPVALUE. So I created a calculated column with this formula, so each line of value now has the conversion factor (depending on the local currency and month)

Worked like a charm. But with a little more study, my goal is not use the calculated column.

[MFelix]

Hi @Anonymous ,

In my case I was abble to do it with a measure. However since I cannot edit your data source I'm unable to tweak some bit the information.

Believe that your issue is with the reporting currency vs the transaction currency, if you only need to have to USD you table currency will only need to have the following:

Report Cur             Transaction Cur           Factor

USD                        ABC                             123

USD                        BCD                             234

ABC                        ABC                              1

BCD                       BCD                              1

So basically USD to every other currency with the factor you need and Every other currency to itself.

check if this works.

Regards,

MFelix