Forum Discussion
Getting Wrong Grand Total
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.
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] ) )
3 Replies
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] ) )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
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
