Getting Wrong Grand Total

[Ben9987]

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.

[v-shex-msft]

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

[tamerj1 · Accepted solution]

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] ) )

[sanalytics]

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