Forum Discussion
Matrix subtotal
VoltesDev Matrix and table totals calculate the given measure in the context of ALLSELECTED essentially. They do not calculate a sum or an average per se. You need to build that into your measure. Often, using HASONEVALUE to determine if you are in a row or a total row, you then can use SUMX or AVERAGEX for the total.
Hi VoltesDev
Use ISINSCOPE() to control subtotal logic
You can return
Normal value at row level
BLANK() at subtotal level (or custom logic)
Example
Measure=
IF(
ISINSCOPE(Dim[RowField]),
[Base Measure],
BLANK()
)or
Custom Avg subtotal via DAX
If you want Avg at subtotal, Sum at rows
My Measure =
IF(
ISINSCOPE(Dim[RowField]),
[Value],
AVERAGEX(
VALUES(Dim[RowField]),
[Value]
)
)This is average of visible rows, not raw data.
Please mark it as a solution with headup if this helps you. Thank You!
Hi,
Subtotals aggregate is using whatever your measure returns under that filter context. If your measure is SUM(), the subtotal will also be SUM.
However creating a measure that switches behavior at subtotal level, such as using ISINSCOPE() or HASONEVALUE() to detect whether you are at the row item or at a subtotal, can ideally return either the base value or an AVERAGEX() across visible rows.