topic Re: Getting Wrong Grand Total in DAX Commands and Tips

Getting Wrong Grand Total

Ben9987 — Wed, 01 May 2024 04:04:04 GMT

Hello Everyone,

I am having the following issue

i have a table visual in Power BI and i have 2 measures (Measure A,Measure B).These 2 Measure are coming from different fact table.Below is the screenshot attached

Now the Scenario here is for Country 2 since Measure A is blank ,We need to replace Measure B Value in Measure A.So i have used the following Dax function IF(ISBLANK(Measure A),Measure B,Measure A) and have got the following output as shown below

Now what happens is when grand total is being calculated it only gives me the total of Country 1 in Measure A (i.e 100)as shown below but the output should have been 400.

SUMX() resolves the issue but Measure A and Measure B would be used across other dimensions as well.

I am seeking guidance on how best to address this issue and achieve the desired outcome efficiently.

Thank you for your attention to this matter, and I look forward to your guidance.

Re: Getting Wrong Grand Total

sanalytics — Wed, 01 May 2024 10:05:47 GMT

Hello Ben9987

Can you please try the below measures

Total = Sumx ( YourTable, IF(ISBLANK(Measure A),Measure B,Measure A) )

and use this measure in table visual..
If this is not working, requesting you to provide your pbix file with dummy data..

Hope it will help you.

Regards

sanalytics

If it is your solution then please like and accept it as solution

Re: Getting Wrong Grand Total

tamerj1 — Wed, 01 May 2024 10:17:25 GMT

Hi Ben9987
First please vote for this idea by Greg_Deckler

First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This 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/63376

Also, 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/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

In order to force additivity over your measure you need to aggregate the values using SUMX by iterating the distinct visible values of the column (or column combinations) that represents the core structure of your Table or Matrix visual.

MeasureA Correct Total = SUMX ( DISTINCT ( 'Table'[Country] ), COALESCE ( [MeasureA], [MeasureB] ) )

Re: Getting Wrong Grand Total

Anonymous — Fri, 03 May 2024 02:01:13 GMT

Hi Ben9987 ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng