Cumulative Total Giving Unexpected Results

[Timmay]

Please see attached file for details, sheet “cumulative totals”.

I have a cumulative total calculation:

Cumulative Sales =
CALCULATE(
[Sales Total],
FILTER(
ALLSELECTED( ‘Dates’ ),
‘Dates’[Date] <= MAX( ‘Dates’[Date])
)
)

That works fine but when I run the same calculation on a different measure, unexpected results are returned

Cumulative Higher Sales =
CALCULATE(
[higher Sales],
FILTER(
ALLSELECTED( ‘Dates’ ),
‘Dates’[Date] <= MAX( ‘Dates’[Date])
)
)

Notice the image that the "cumulative higher sales column is incorrect on the 7/8/21

The column higher sales takes the higher of “sales total” and “sales LY”

But for some reason the “cumulative higher sales” column returns the exact same result as “cumulative sales”

The attached file is a dummy of a real problem that I have that’s a bit more complicated but hoping that the solution will translate easily enough.

Many thanks,
Tim
Mock Data PBI.pbix (959.7 KB)

[Harsh]

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 -

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)

Fix Incorrect Totals

When using a measure column in a visual, it is not uncommon in Power BI to find that the totals are incorrect. The Fix Incorrect Totals DAX pattern can be used to correct this issue. When a DAX measure is providing the correct detail value it often, unfortunately, provides an incorrect value for the total row. This is due to there being no evaluation context for the total row. For example, when comparing the [Total Sales] to the [Sales LY] to find the minimum for a customer using the simple DAX…

Wrong values on total

Hello @Harris, Thank You for posting your query onto the Forum. Well to fix the totals you’ll just be required to write a small additional formula. One of our expert @Greg has already created a post onto the forum which specifically addresses this type of issue. Below is the link of that post provided for the reference purpose. But when you write the formula you’ll see that how the context at the back-end is getting evaluated. On the visual you’re getting the results as “2.38%”. But at the ba…

Group By Division, IF Statement & Proper Subtotal Calculation

Hello @Helios, The reason why second formula is giving inflated results is because it is first considering the sum of each and every individual row level as per “VAR X” i.e., “COLD CALL = 256.90” and “EMAIL = 1,047.90” and then when you added the another condition i.e., “VAR FINAL”, it just summed up the results by 2x. So since previously you had the wrong total as “1,304.80”, now because of that additional variable which you added inside the same formula it inflated the results. It doesn’t m…

Exclude Zero results in Overall Calculation?

Hello @gkavesh, Thank You for posting your query onto the Forum. Well just couple of days back I exactly addressed this same kind of issue onto the forum. Below is the link of that post provided for the reference. Please feel free to check out that post. Here’s your solution provieded below - (Let’s analyse this step-by-step) 1). The formula which you had used for “Productivity Rate” is as follows - Productivity Rate = DIVIDE( [Arrived - 1] , [Clinic Hours] , 0 ) Now, since you’ve complet…

[Timmay]

That’s fantastic thanks Harsh.
As my actual problem was a bit more complex I ended up looking at one of the other posts and using the sumx - values and addcolumns to get the max of either amounts and then doing a cumulative total on that.

All solved in the end, thanks again.