Forum Discussion
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
- nickc_innovaAdvocate I
Nevermind,
Solved it myself with the following code:DynamicSubtotal =SUMX(CALCULATETABLE('Fact Table', ALLSELECTED('Fact Table'[Category])),('Fact Table'[Values])) - amitchandakSuper User
nickc_innova , Kudos to you