Subtotal as a sum of its parts

[Anonymous]

Hello,

I am having issues with forcing my subtotals to equal the sum of its sub parts as opposed to a measure performed on the subtotal. I am calculating the difference in quotes between quarters. For each individual item, the measure is calculating correctly, however when rolled up to the location it ends up taking the difference between the total quotes for the location, rather than a sum of the individual items. 

My measure is below (using dummy data):

Quote Difference = 
VAR _CurrQ = SUM('Table'[Quote]) 
VAR _PrevQ = CALCULATE(SUM('Table'[Quote]), DATEADD('Table'[Date], -1, QUARTER)) 
VAR _Diff = IF(_CurrQ && _PrevQ, SUMX(VALUES('Table'[Description]), _PrevQ-_CurrQ)) 
RETURN

_Diff

This is the result. The second table is just to show the quotes side by side. The Denver total should be 40, and the Seattle total should be 0. 

Thank you in advance!

Best,

Alex

[Anonymous · Accepted solution]

@Anonymous -

Try the following. The first CALCULATE is there to force context transition, which @jdbuchanan71 alluded to. 

Quote Difference = 
VAR _Table1 =
    ADDCOLUMNS (
        VALUES ( 'Table'[Description] ),
        "CurrQ", CALCULATE ( SUM ( 'Table'[Quote] ) ),
        "PrevQ", CALCULATE ( SUM ( 'Table'[Quote] ), DATEADD ( 'Table'[Date], -1, QUARTER ) )
    )
RETURN
    SUMX (
        _Table1, 
        IF ( [CurrQ] && [PrevQ], [PrevQ] - [CurrQ] )
    )

I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.

[jdbuchanan71]

@Anonymous 

I believe you are running into problems with variables and context transition.  Can you try splitting it into three measures?

Quote Amount =  SUM('Table'[Quote]) 

PQ Quote Amount = CALCULATE( [Quote Amount], DATEADD('Table'[Date], -1, QUARTER)) 

Quote Difference = SUMX(VALUES('Table'[Description]), [PQ Quote Amount] - [Quote Amount] )

Calling measures in the SUMX will force the context transition.