Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Subtotal as a sum of its parts

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. 

Capture1.PNG

 

Thank you in advance!

 

Best,

Alex

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@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.
Nathan Peterson

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

@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.
Nathan Peterson
jdbuchanan71
Super User
Super User

@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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.