Cumulative Total Giving Unexpected Results

DAX DAX Calculations
2

Hello @Timmay,

Thank You for posting your query onto the Forum.

The reason why you’re getting the incorrect figure for “Cumulative Higher Sales” measure is because check the “Grand Total” of the “Higher Sales” measure which you’ve created. Although, the measure is providing correct results at an individual row level but the grand total result is incorrect i.e., it’s the same result as “Sales Total” measure.

And to fix the grand totals issue, you’ll be required to write a small measure as provided below -

Higher Sales - Correct Totals = 
SUMX(
    SUMMARIZE(
        Dates , 
        Dates[Date] , 
        "@Totals" , 
        [Higher Sales] ) , 
    [@Totals] )

And now, reference the above provided measure inside the “Cumulative Higher Sales” measure by replacing the “Higher Sales” measure.

Cumulative Higher Sales = 
CALCULATE( [Higher Sales - Correct Totals] ,
    FILTER( ALLSELECTED( 'Dates' ) ,
        'Dates'[Date] <= MAX( 'Dates'[Date] ) ) )

Below is the screenshot of the final results provided for the reference -

Final Results
Final Results866×979 55.6 KB

One of our expert @Greg had already created a post onto the forum pertaining to the topic - “How To Fix Incorrect Totals”. Below is the link of that post provided for the reference.

To learn more about why this problem occured and how to fix it, I’ve already provided solutions onto this topic onto the forum about how results are evaluated at the back-end, in the case of - “IF()” function or “SWITCH( TRUE() )” statement.

I’m also attaching the working of the PBIX file alongwith the links of those posts for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Mock Data PBI - Harsh.pbix (959.6 KB)

1 Like