Forum Discussion
Subtotal not working properly
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
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
2 Replies
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
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