Forum Discussion
Power BI - Matrix : Column Header Grouping for multiple measures
Hi All,
I have to build a matrix report as shown in the image below. The challenge is that I'm not sure how to custom group multiple measures in the column header. For example, there are three measure grouped under the group 'HEAD COUNT' and four measures grouped under 'BUDGET AND FORECAST'. I've tried unpivoting the measures, created a custom column to group them and used 'value' column in the 'values' field of the matrix and it works. However the downside/challenge with this approach is that I lost the ability to conditionally format each measure column (that is because I now only have one 'value' column with the "unpivot" approach). Also, the other issue I've noticed with this approach is that the corner of the Matrix visual displays the name of the header grouping column (upon expanding the hierarchy to all levels) and I'm not sure how to remove it.
Could someone please advise any other alternative method/approach to it?
Hi， Anonymous ；
You should create a table like below:
Then create a measure.
Measure = SWITCH(MAX('Table2'[Measurename]),"FTE COUNT",CALCULATE([Measure 1]),"TEMP COUNT",[Measure 2],"BUDGET TMPS",[Measure 3],"FTE",[Measure 4],"FORE",[Measure 5])The final show：
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
17 Replies
Hi， Anonymous ；
You should create a table like below:
Then create a measure.
Measure = SWITCH(MAX('Table2'[Measurename]),"FTE COUNT",CALCULATE([Measure 1]),"TEMP COUNT",[Measure 2],"BUDGET TMPS",[Measure 3],"FTE",[Measure 4],"FORE",[Measure 5])The final show：
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.I'm tyring to recreate this and it seems like maybe they killed support for this workaround as I have to drill down to view my column headers. I downloaded your pbix file and when it worked fine until I removed the columns and value and put them back, the same thing happened where I had to drill down to see the sub colum headers. Any advice on this?
One way is to create a table with the measure names. See if this helps:
I've tried the approach specified in the article. It appears to work when the 'Column Subtotals' and 'Row Subtotals' are turned off (Matrix-1 in the image below). However, data in the column 'Forecast' and the second subtotal seems to be blank/missing (Matrix -2 in the image below). Also, on top left corner of the Matrix, I see the label 'GROUPING' (which is a column name) which I'm not sure how to hide it.
Below is the link to PBIX file. Please advise.
https://drive.google.com/file/d/1w-4V7miXPFqrs7rDq0vehe-1dMT-4i-7/view?usp=sharing
First of all, you may not actually need a hybrid matrix structure.
In the hybrid table, the row totals work by default. The column totals will need some DAX, but first you need to define what totals you wish to show.
Thanks for your response!
The article link that you've sent me suggests creating a 'hybrid' table for column groupings. In this case, the columns Sales and Target are grouped under 'Actuals' and the columns Budget and Forecast are grouped under 'BUD & FORC'. Are you suggesting that there may be other options to achieve the same results without a hybrid table?
In this example, the data is probably not the most suitable example, but I'd like to see the data that is missing from Matrix - 2 (outlined in the image below) as well as the totals for column groupings and the grand total for all columns.
Also, with this hybrid approach, since there is only one ‘Values for Matrix’ column in the values field of the Matrix, how do I apply different conditional formats to each column in the visual?
There is a label 'GROUPING' (which is a column name) on the top left corner on the Matrix that I'm not sure how to hide or remove it?
Hi. Great solution. Is there any other way to achieve the same results without switch?
