Forum Discussion
Helper IIIRow Level Totals Blank when adding Measure Names to rows in Matrix visual
I have a matrix visual where I added ProviderName and MeasureName to rows, Year-Month to columns and values based on a calculated measure.
The calculated measure :
I want just the grand totals and no subtotals. I think the issue is because the measures I am using are not aggregating as expected across the row and column context.
The output should be like this, at the same time i want to filter the measures.
8 Replies
- Deku
Super User
You are using Selectedvalue on the measure name, on the grandtotal the measure name is not filtered and you have all three measures in scope, resulting in blank. Therefore you switch goes to the else statment, returning blank
- Deku
what would be expected for the grandtotal? the sum of all of the three measures? do grand totals make sense in this situation?
v-hashadapuCommunity Support
Hi Kalaivani , Thank you for reaching out to the Microsoft Community Forum.
Please let us know if your issue is solved. If it is, consider marking the answers that helped 'Accept as Solution', so others with similar queries can find them easily. If not, please share the details.
Thank you.- v-hashadapu
Hi Kalaivani , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you. - v-hashadapu
Hi Kalaivani , Thank you for reaching out to the Microsoft Community Forum.
Try below example DAX measure (may need tweaking):
SelectedMeasureCompareOptimized =
IF(
HASONEVALUE(MeasureSelector[MeasureName]),
SWITCH(
VALUES(MeasureSelector[MeasureName]),
"Wrvus", [PivotWrvusCompare],
"Wrvus per Visit", [Wrvus per Visit Compare],
"Deficit Wrvus to 65th %tile", [DeficitWrvus65thcalCompare],
BLANK()
),
IF(
ISINSCOPE(MeasureSelector[MeasureName]),
CALCULATE(
SWITCH(
MAX(MeasureSelector[MeasureName]),
"Wrvus", [PivotWrvusCompare],
"Wrvus per Visit", [Wrvus per Visit Compare],
"Deficit Wrvus to 65th %tile", [DeficitWrvus65thcalCompare],
BLANK()
),
ALLSELECTED(ProviderName)
),
BLANK()
)
)
In your matrix, keep ProviderName and MeasureName in the Rows, Year-Month in the Columns, and use the updated measure below in the Values. In the Format pane, turn off subtotals for ProviderName (but keep grand totals on). Finally, add a slicer for MeasueSelector[MeasureName] so users can choose between Wrvus, Wrvus per Visit, and Deficit Wrvus.
If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you. - v-hashadapu
Hi @Kalaivani , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.
