Forum Discussion
Matrix subtotal
Hi,
Just wondering if the current Power BI Matrix Subtotal have the ability to change Sum to Avg.
Also if I have 3 columns, then I turn on Row Subtotal, can we select to not calculate some of the column ?
This is related to that AVG, which if cannot calculate Avg instead total, it is better to don't show any value in that column, but still totalling other columns
Thanks,
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!
4 Replies
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,
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.
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.
