Totals not adding up in PBI Table with SUMX and/or nested measures

[Anonymous]

Hi All,

I am facing an issue where the totals are not adding up correctly in the table visual. I have already tried out these solutions 

1. https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

2. https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

plus some other attempts but I have not been able to fix it.

The issue is in the potential_savings_1 and potential_savings_2 measures. these are my attempts to get the measure to work. I just need to fix either one of them.  I think the issue comes up because each measure uses the previous measures value.

the measures are:

1. unscheduled orders

% unscheduled orders = 

var unscheduled = CALCULATE(COUNTROWS(data), data[ScheduledUnscheduledFlag] = "Unscheduled", data[Contract Category] = "Private Contract")

var scheduled = CALCULATE(COUNTROWS(data), data[ScheduledUnscheduledFlag] = "Scheduled", data[Contract Category] = "Private Contract")

RETURN ((unscheduled) / ((unscheduled) + (scheduled)))

2. unscheduled cost

unscheduled cost = CALCULATE(SUM(data[Cost]), data[Contract Category] = "Private Contract", data[ScheduledUnscheduledFlag] = "Unscheduled")

3. unsheduled best practise

unscheduled_best_practise = 

var min_val = CALCULATE(MINX(VALUES(data[Area]), [% unscheduled orders]), REMOVEFILTERS(data[Area], data[Continent]))

RETURN if( min_val < 0.05, 0.05, min_val)

4. potential savings (attempt) 1: [Problem is here]

potential_savings_1 = 

( 
    ([unscheduled cost] * 
        ([% unscheduled orders] - [unscheduled_best_practise])
    )
    /2
)

5. potential saving (attemp) 2: [Problem is here]

potential_saving_2 = IF(COUNTROWS(VALUES(data[Area]))=1, [potential_savings_1], SUMX(VALUES(data[Area]), [potential_savings_1]))

I have also attached a PBIX file with sample data -  https://drive.google.com/file/d/1xOQyMCFi-VO_pWdFxuSkXcbp-QKKpjiM/view?usp=sharing.  Any help with fixing these measures is greatly appreciated! Thank you!

[arif_ali]

Hello,
I am running to the same issue where grand total is not adding up. I tried the following to measures and both are giving me the same results. Here are the measures and the screenshot of results. The results should be 19,911. Can someone assist please?

Rebate Payout 1 =
VAR mRebateBucket1 = [Bucket1]
VAR mRebateBucket2 = [Bucket2]
VAR mRebateFinal = IF(mRebateBucket2>mRebateBucket1, mRebateBucket2, mRebateBucket1)
VAR mRebateAmount = [Total Sales] * mRebateFinal
RETURN
mRebateAmount

Rebate Payout 2 =
VAR mRebateBucket1 = [Bucket1]
VAR mRebateBucket2 = [Bucket2]
VAR mRebateFinal = IF(mRebateBucket2>mRebateBucket1, mRebateBucket2, mRebateBucket1)
VAR mRebateAmount = SUMX('Trans Table',[Total Sales] * mRebateFinal)
RETURN
mRebateAmount

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

You can create another new measure as below to get the correct total value and put the new measure onto the visual to replace the measure [potential_savings_1]. You can find the details in the attachment.

Npotential_savings_1 = 
SUMX (
    GROUPBY ( 'data', 'data'[StandardizedProduct], 'data'[Area] ),
    [potential_savings_1]
)

In addition, you can refer the method in the following links to resolve it.

Why Your Total Is Incorrect In Power BI - The Key DAX Concept To Understand

Dax for Power BI: Fixing Incorrect Measure Totals

Best Regards

[amitchandak]

@Anonymous , You have to try measure like

potential_savings_1 =
sumx(summarize(data,data[Area], Data[standardizeproduct], "_1",
(
([unscheduled cost] *
([% unscheduled orders] - [unscheduled_best_practise])
)
/2
) ) ,[_1])

[Anonymous]

Hi @amitchandak 

thanks for looking into this

When I apply the measure you shared (potential_savings_3) the row wise calculation does not look correct. The row values in potential_savings_2 is what I would expect in the potential_savings_3 as well, though the new column does total correctly.