Forum Discussion
Matrix visualization - limitations / alternatives
Thank you for your suggestions. The suggestions above regarding using the Average function are correct, but I was blocked by a previous step.
I found the solution for the part that was tripping me up. (With assistance; thank you to Microsoft resource.)
Our matrix looked like this:
When I tried to enable "Row subtotals", it totally messed up the visualization, like this:
...so I just disabled "Row subtotals". That was my mistake.
I needed to enable "Row subtotals", and enable "Per row level", and then disable the subtotal for every row except "Case Number".Then I finally saw the matrix appear correctly, with the row along the bottom.
Now I was finally ready to make the change suggested above, to change the "Value" from the "[Sum of] DaysInStatus" to the "Average of DaysInStatus". After doing that, and updating the label, it was ready.
(Note, changing that from sum to average affects not only the subtotal row, it also affects the cells in the matrix. That was okay because our source data is already aggregated.)
Final note, regarding the CSV export - I received confirmation (from the Microsoft PowerBI expert) that no, the matrix cannot simply export to CSV directly in the pivoted format.
Community SupportHi Anonymous ,
We create a sample using your source data.
If you want to get the average in Total, you can configure the days in status to be averaging.
Or you can use this measure,
Measure =
IF(
HASONEVALUE('Case'[case number]),
SUM('Table'[daysinstatus]),
AVERAGE('Table'[daysinstatus]))
If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.
Thank you for your suggestions. The suggestions above regarding using the Average function are correct, but I was blocked by a previous step.
I found the solution for the part that was tripping me up. (With assistance; thank you to Microsoft resource.)
Our matrix looked like this:
When I tried to enable "Row subtotals", it totally messed up the visualization, like this:
...so I just disabled "Row subtotals". That was my mistake.
I needed to enable "Row subtotals", and enable "Per row level", and then disable the subtotal for every row except "Case Number".
Then I finally saw the matrix appear correctly, with the row along the bottom.
Now I was finally ready to make the change suggested above, to change the "Value" from the "[Sum of] DaysInStatus" to the "Average of DaysInStatus". After doing that, and updating the label, it was ready.
(Note, changing that from sum to average affects not only the subtotal row, it also affects the cells in the matrix. That was okay because our source data is already aggregated.)
Final note, regarding the CSV export - I received confirmation (from the Microsoft PowerBI expert) that no, the matrix cannot simply export to CSV directly in the pivoted format.
- v-zhenbw-msft
Hi Anonymous ,
Glad to hear that you have solved your issue.
Please kindly mark your reply as a solution to help others find it more quickly and end this thread.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.