Sum of subtotals is greater than the grand total

[kjel]

Hi, I have a measure which gives me the correct value for the grand total but when I try splitting it down into subtotals, the sum of the subtotals becomes greater than the grand total. I'm fairly sure that I'm messing something up w.r.t. the filter context but I'm fairly new to DAX and I can't find the error.

I have these two measures set up to calculate the LTM sum of my sales, meaning that for every month I want to calculate the total sales in that month and the previous 11 months. Pretty simple for historical months but in the current month I want to add my forecasted numbers to my actuals and in future months I want to show the forecasted numbers. These are the measures:

Remaining Sales Budget =

    CALCULATE(

        MAX( [Sales Budget Amount] - [Sales Amount],0 ),

        KEEPFILTERS( 'Date'[DateTime] >= DATE(YEAR(TODAY()),MONTH(TODAY()),1) )

    )

Sales&Budget LTM =

    CALCULATE(

        [Sales Amount] + [Remaining Sales Budget],

        DATESINPERIOD( 'Date'[DateTime],MAX('Date'[DateTime]),-12,MONTH )

    )

I hope my explanation makes sense. Anyway, I've checked and I get the correct LTM totals when using these measures UNTIL I try to create a visual that splits the total sales amount, for example by customer segment or product type.

The total at the bottom is the correct number, however the sum of the subtotals is 343,645,368 (not USD fyi)

Any ideas on how to fix this? Let me know if I need to provide more information 🙂

[v-jayw-msft]

Greate job!

[kjel · Accepted solution]

Just wanted to add that I've resolved the issue, it was pretty simple: when looking at the totals I either under/overshoot my forecast and the LTM reflects that, however when I split the actuals and forecasted numbers I can underperform in some categories while overperforming in others and the MAX() function then makes the sum of the subtotals larger than the "grand total".