Create a measure that sums different currencies

[Anonymous]

Hello all,

I have a table with several values in different currencies (7 currencies in total).

How do I get the total sum of a HeadCount, considering the currency conversion?

Eg.: I would like to get the sum of all the values from the attached table in dollar.

Any idea?

SA_Budget

[Anonymous]

Hi, for your query, first i've performed "unpivot" transformation to dates column in "Arg_Orçado" table. After, i've created the relationship between "ID_moeda" and "currency" columns. Then i've created "DOLLAR" measure for aplied the exchange rate to the values.

anyway, this is the pbix that i've worked:

EXCHANGE RATE

[tarunsingla]

Converting other currencies to USD is clear, but how is HeadCount dependent on Currency Conversion?

Or are you looking for the Sum of HeadCount for records that are in USD.

Regards,

Tarun

[Anonymous]

Really, my doubt is very vague. I will try to explain my situation better now.

I have a spreadsheet with several values budgeted of South America. There are 7 countries in total (Brz, Arg, Chi, Col, Per, Ecu, Uru). The budgeted amounts are in the local currencies of the respective country. That is, 7 different currencies.

My idea is to create 5 tabs:

South America

Brz 

Arg, Chi, Uru

Col, Ecu

Per

To get the sum of the total budgeted for HeadCount, I created a measure using CALCULATE and SUMX (after I unpivot the columns):

Total Budget = CALCULATE(SUMX('SA_Budget','SA_Budget'[Value]))

But this formula does not consider the currency conversion. I am adding Argentine Peso with Brazilian Real, with Chilean Peso, etc...

In the tab "South America", I want to show the Total Budget Amount (Brz+Arg+Chi+Col+Per+Ecu+Uru) in dollar.

In the Brz tab, I want to show only the Brazil Total Budget Amout (in reais).

And so on...

My spreadsheet is basically this: SA_Budget_Currency

Is it clearer now? I started using PowerBI for 1 month, but the DAX formulas are still a mystery to me (for now).

[v-chuncz-msft · Accepted solution]

@Anonymous ,

You may use LOOKUPVALUE Function to add a calculated column in DAX.

http://community.powerbi.com/t5/Desktop/Excurr/m-p/361854#M163415