Subtotal not working properly

[Anonymous]

Hi all!

I'm trying to get the total sum of a table, which has values in different currencies.

I started learning DAX two weeks ago but I managed to get the correct sum by month, but the subtotals are not working.

Basically...

I have a table with the value of each currency in dollar:

To get only the values in the ARS currency, I used:

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

To convert ARS to USD:

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

And finally, to get the total amount corrected:

Total_ARS_USD = DIVIDE([Total_Orçado_ARS];[Convert_ARS_USD];0)

The result per row appears correct, but the subtotal does not.

1) How can I get the correct subtotal (8.215.067,05 ARS)?

2) Any suggestions on how I can optimize this currency conversion?

Best Regards

[v-xicai · Accepted solution]

Hi @Anonymous 

For your question1 ,this looks like a measure totals problem. Essentially, create a "Calculate_Row" measure that calculates correct result at the row level.

Calculate_Row = DIVIDE([Total_Orçado_ARS],[Convert_ARS_USD],0)

Then, create a "Total_USD" measure that performs a SUMMARIZE of your data, exactly as how it is displayed in your table, and use the "Total_USD" measure within that SUMMARIZE function to provide the values for the individually summarized rows. Finally, perform a SUMX function across that summarized table to employed in the Total line. The HASONEVALUE function check whether it is really necessary to use that the SUMX.

Total_USD =

VAR _table = SUMMARIZE('db_Currency',[Mes_Nome_Mai],[Total_Orçado_ARS],[Convert_ARS_USD],"_Value",[Calculate_Row])

RETURN

IF(HASONEVALUE('db_Currency'[Mes_Nome_Mai]),[Calculate_Row],SUMX(_table,[_Value]))

Refer to this post about similar case: https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907 .

For your question 2, you can create a "Convert_ARS_USD" measure to get currency rate.

Convert_ARS_USD = LOOKUPVALUE(db_Currency[Factor],Arg_Orcado[Currency],"ARS",Arg_Orcado[Mes_Nome_Mai],MAX(Arg_Orcado[Mes_Nome_Mai]))

If you have any other issue, please feel free to ask.

Best Regards,

Amy

[Anonymous]

Hey @v-xicai 

Digging a little more I discovered the LOOKUPVALUE. So I created the 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 I will look the SUMMARIZE and HASONEVALUE formula later.

Thanks for the help