incorrect Grand Total

Question

My Dax is returning&nbsp; incorrect Grand Total.&nbsp; Can anyone help review or rewrite it.&nbsp;Demo&nbsp;=IF&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;HASONEVALUE&nbsp;(&nbsp;'Report&nbsp;Currency'[Report&nbsp;Currency]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;SUMX&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Exchange&nbsp;Rates',&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUMX&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Calendar',&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;VALUES&nbsp;(&nbsp;'Exchange&nbsp;Rates'[exchange_rate]&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;*&nbsp;DIVIDE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUM&nbsp;(&nbsp;'Daily&nbsp;Item&nbsp;Sales'[total_amount_home]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUM&nbsp;(&nbsp;'Daily&nbsp;Item&nbsp;Sales'[qty_sold]&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;DIVIDE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUM&nbsp;(&nbsp;'Daily&nbsp;Item&nbsp;Sales'[total_amount_home]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUM&nbsp;(&nbsp;'Daily&nbsp;Item&nbsp;Sales'[qty_sold]&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;))

greg_deckler · Answer

Anonymous&nbsp;You might try ISINSCOPE instead of HASONEVALUE. Also, I would create your measure for your rows and then create a separate measure that uses that measure that returns either the row value or the total value depending on whether you are in a row or total row.
&nbsp;
First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742eThis looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907Also: https://youtu.be/uXRriTN0cfYAnd: https://youtu.be/n4TYhF2ARe8

anonymous · Answer

Greg_Deckler&nbsp; The Isinscope did not help.&nbsp;&nbsp;

tamerj1 · Answer

Hi&nbsp;Anonymous&nbsp;
Brother, would you please place a screenshot of your data model along with the relationships?

anonymous · Answer

tamerj1&nbsp; The relationship&nbsp; is 1: *&nbsp; live connection.&nbsp;

tamerj1 · Answer

Anonymous&nbsp;
The formula contains three tables. Please provide complete information&nbsp;

Forum Discussion

incorrect Grand Total

6 Replies

Related content

Incorrect turnover total

Incorrect Grand total?

Incorrect Grand total value

Measure Total Incorrect

Row-Level Calculations Correct but Grand Total Incorrect – VM Utilisation