Forum Discussion

nickc_innova's avatar
nickc_innova
Advocate I
3 years ago
Solved

Contexual Subtotal

Looking to have a measure return a sum that retains the context of a slicer but ignores the context of the row on a table visual. 
Here is a simple example table of data:


Here is a simple table visual that intrinsically summarizes the values. With no Category filter applied the sum is 51. 

Filter the table to Animals and Colors, and the new total is 29.

 

I need a measure that will return these totals from the slicer but ignore the row-level context of the table visual. 

I tried variations on the following code:
DynamicSubtotal = 
CALCULATE(
SUM('Fact Table'[Values]),
ALLSELECTED('Fact Table'[Category]),
VALUES('Fact Table'[Category])
)

 


But I can only return the sum of all Categories ignoring the slicer, or all categories including the context of the slicer and the row. How do I keep the slicer context but not the row context?

Expected result:

 

Thanks in advance

  • Nevermind, 
    Solved it myself with the following code:

    DynamicSubtotal =
    SUMX(
    CALCULATETABLE('Fact Table', ALLSELECTED('Fact Table'[Category])),
    ('Fact Table'[Values])
    )

2 Replies

  • Nevermind, 
    Solved it myself with the following code:

    DynamicSubtotal =
    SUMX(
    CALCULATETABLE('Fact Table', ALLSELECTED('Fact Table'[Category])),
    ('Fact Table'[Values])
    )