Matrix with grouping per column and per rows with multiple grand totals in column

[reshka]

Hi,

I've been trying to set up a matrix visual in PBI to get a layout quite easily achieveable in Excel but apparently not in PBI, at leats to the best of my knwoledge. Before I devote to a work-around I was hoping some one here can change my mind and deterime it is in fact doable 🙂

The core of the issue is as follows. I have a dataset with unique values per combination of ID and category. I need to get a matrix visual where in rows I show categories and in columns IDs (they're actually unique values so any type of aggregation does the trick). Then, I need to show multiple aggregations per each row, e.g. minimum, maximum, average. The problem of course is that if I select multiple measures as values, then I'll get multiple values not only in Grand Total but also in columns by ID where I would want to see single values only.

Here's how it can be visualized:

Would you happen to know if/how such layout can be set up in PBI?

Your help will be very appreciated!

Best regards,

Kasia

[Vera_33 · Accepted solution]

Hi @reshka 

Here is one way, create a disconnected table for the column in the matrix

Then do SWITCH to call your other measures

[reshka]

Dear @Vera_33,

Thank you for your suggestion! It's very helpful. I'm not sure, however, if it's going to work 100% for me, as I can't have aggregated values to be fixed.

What I apparently failed to describe in my original post is that the groups of IDs may be changing. In fact, ID's will also belong a hierarchy like Town or Country and in one set it may be needed to show results of a single town or multiple town, with a dynamic filter at work. Or, with this use case, I may want to see the stats only for selected IDs. If I'm not mistaken, that would not work with such a disconnected table.

Best regards,
Kasia

[Anonymous]

Hi @reshka ,

Based on my test, @Vera_33 's solution worked and then you can also filter the ID dynamically, and the results will change dynamically accordingly.

If you are still confused, please provide more detailed sample data and expected results.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.