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 ,
You can configure the average of days and sort by it.
But you cannot export the same structure as the matrix table, you just can export as the source data.
If you want to export the matrix-seen, you need to pivot it in Query Editor.
Then you can export the data like the matrix structure.
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, however, those solutions do not seem to work in our situation. I'll start by mentioning we're not using imported data, we're using liveconnect to AAS; I think that creates limitations for us.
Those steps show how to configure the Value to be an average, but we don't want the Value (displayed in every cell of the matrix) to be an average, we want the row of averages along the bottom.
For reference, our source data is shaped like this:
Those steps show in a screenshot a row of averages along the bottom, but we cannot find how to do that.
We've try enabling both "Row subtotals" and "Column subtotals", and neither adds a footer row of averages. Are we missing another configuration step?
Finally, the instructions show how to pivot it in the Query Editor, but we cannot do that, because we're using liveconnect to AAS, not imported data. We control the AAS model and we could reshape the AAS model, but we did not pivot it in the AAS model because in our understanding there's no other PowerBI visualization that could support the dynamic columns, other than the matrix.
- v-zhenbw-msft
Hi 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.