Forum Discussion
Matrix subtotal
- 7 months ago
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.
- 7 months ago
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 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 guys,
Thanks for the input.
The measures are good, hopefully that matrix or table visual have this setting in the future. Having measure sometime can break or need extra maintenance.
Thanks again.