Why does my calculation require SUMX in a second measure to total correctly?

[AUaero]

Hi,

I'm having trouble understanding why SUMX is behaving this way.  I'm trying to calculate the tons sold to customers that have their first invoice date in the previous 12 months.  My calculation works fine at the month level, but I get an incorrect total in my table visual.  When I apply SUMX in my original measure, I get a wildly incorrect result.  But when I apply SUMX in a second measure that totals up the first measure, the totals are correct.  Why is applying the SUMX in a second measure different that including SUMX in the first?

I"m including a link to a sample PBIX to illustrate.
https://1drv.ms/u/s!AtHPCBKFQogGgst3YGf2Ov9WHXxK0A?e=7GsoHP

Here's a screenshot of the results:

Measures:

Initial measure to calculate tons sold by month for accounts that have their first invoice in the previous 12 month period:

# New Customer Incentive Tons = 
VAR _AccountsWithFirstInvoiceDate = 
FILTER(
    SUMMARIZE(
        Customers,
        Customers[CustomerAccountNumber],
        Customers[FirstInvoiceDate]
    ),
    NOT(ISBLANK([FirstInvoiceDate]))
)

VAR _EligibleAccounts = 
CALCULATETABLE(
    FILTER(
        _AccountsWithFirstInvoiceDate,
        [FirstInvoiceDate] IN DATESBETWEEN(
            'Calendar'[DATE_DESC],
            EOMONTH(MAX('Calendar'[DATE_DESC]), -12) + 1, 
            MAX('Calendar'[DATE_DESC])
        )
    )
)

VAR _EligibleAccountsData = 
ADDCOLUMNS(
    _EligibleAccounts,
    "Tons", CALCULATE(
        Sales[# Billed Tons]
    )
)

RETURN
SUMX(
    _EligibleAccountsData,
    [Tons]
)

 As you can see in the screenshot, the total for this measure is 1,400.43 which is incorrect.  It should be 2,678.92 if you add up the values in the column.

To correct the incorrect total, I tried applying a SUMX to the results calculation that was returned by the first measure.

# New Customer Incentive Tons Include SUMX = 
VAR _AccountsWithFirstInvoiceDate = 
FILTER(
    SUMMARIZE(
        Customers,
        Customers[CustomerAccountNumber],
        Customers[FirstInvoiceDate]
    ),
    NOT(ISBLANK([FirstInvoiceDate]))
)

VAR _EligibleAccounts = 
CALCULATETABLE(
    FILTER(
        _AccountsWithFirstInvoiceDate,
        [FirstInvoiceDate] IN DATESBETWEEN(
            'Calendar'[DATE_DESC],
            EOMONTH(MAX('Calendar'[DATE_DESC]), -12) + 1, 
            MAX('Calendar'[DATE_DESC])
        )
    )
)

VAR _EligibleAccountsData = 
ADDCOLUMNS(
    _EligibleAccounts,
    "Tons", CALCULATE(
        Sales[# Billed Tons]
    )
)

VAR _Result = 
SUMX(
    _EligibleAccountsData,
    [Tons]
)

RETURN
SUMX(
    'Calendar',
    _Result
)

This measure gives inaccurate results, not just for the total but for the values calculated each month.

Finally, I tried creating a new measure that applies SUMX to the # New Customer Incentive Tons measure.  

# New Customer Incentive Tons SUMX in New Measure = 
SUMX(
    'Calendar',
    Sales[# New Customer Incentive Tons]
)

This measure gives the correct result for each month and totals correctly.

In my mind, the second measure I posted above (# New Customer Incentive Tons Include SUMX) should behave identically to the third measure (# New Customer Incentive Tons SUMX in New Measure), but clearly it does not.  What am I not understanding?

Thanks!

[AntrikshSharma · Accepted solution]

@AUaero The problem with your code is that at each individual cell in the Matrix you go back 12 months and get the Tons, but at the grand total the only filter is 'Calendar'[TRAILING_12_INCL_CUR_MO_FLAG] = 1 so there is no filter in the filter context for MONTH_KEY, what you need is to provide the right granularity at the grand total, which you have already done in the last formula, but you can make it better by using this:

# New Customer Incentive Tons Ant = 
SUMX ( 
    VALUES ( 'Calendar'[MONTH_KEY] ),
    VAR CurrentDate = MAX ( 'Calendar'[DATE_DESC] )
    VAR LastYear = EOMONTH ( 'Calendar'[MONTH_KEY], -12 ) + 1
    VAR AccountsWithFirstInvoiceDate =
        FILTER (
            SUMMARIZE (
                Customers,
                Customers[CustomerAccountNumber],
                Customers[FirstInvoiceDate]
            ),
            NOT ( ISBLANK ( [FirstInvoiceDate] ) )
                && Customers[FirstInvoiceDate] <= CurrentDate
                && Customers[FirstInvoiceDate] >= LastYear
        )
    VAR EligibleAccountsData =
        ADDCOLUMNS ( 
            AccountsWithFirstInvoiceDate, 
            "Tons", [# Billed Tons] 
        )
    VAR Result =
        SUMX ( EligibleAccountsData, [Tons] )
    RETURN Result
) 

[Greg_Deckler]

@AUaero It really depends on the calculation and yours are complex enough that it is difficult to know for sure. In most cases, if the calculation is simple enough, you can use a SUMX in a single measure but once calculations become more complex it is rarely a solution. More information below plus, please vote for the idea to make this far easier in the future!

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

[AUaero]

Thanks, @Greg_Deckler  - you are correct, this is a measure total problem and I agree with your assessment that as measures get more complex, the ol' "throw-a-SUMX-at-it" solution doesn't work.  What I'm trying to understand is why it doesn't work.  I'm just not seeing the functional difference between the second measure I posted that includes the SUMX of the result in a single measure versus the third measure which moves the SUMX to a completely separate measure.  Even when I try using Jeffrey Wang's excellent DAX debug tool to view the EVALUATEANDLOG output I don't see what is driving this behavior.

For now, busting it up into two measures get me an acceptable result so that's what I'll go with, but I hate having to write two measures when I could have done it with one.

[Greg_Deckler]

@AUaero Took a closer look. OK, the reason is that SUMX('Calendar', __Result) doesn't recalculate __Result in the context of 'Calendar'. Variables are static (trust me, I get the irony). Thus, when __Result is calculated, it's value is fixed. Conversely, in the second measure, the first measure actually gets recacalculated in the context of 'Calendar'. 

[AUaero]

Thanks @Greg_Deckler - you're explanation is helpful.  The static nature of variables has bit before, so I guess I should have looked there first.

After refactoring to eliminate the variables, the measure works as expected, but the lack of variables definitely makes it harder to follow.  It did, however, make it clear that I'm creating nested SUMX statements, which explains the slow performance of this measure when I run it against a large row count.

[Greg_Deckler]

@AUaero Oh the joy of measure totals!