Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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?
Solved! Go to Solution.
Hi, @ippman ;
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.
Hi. Great solution. Is there any other way to achieve the same results without switch?
Hi, @ippman ;
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?
Does expanding all down one level in the hierarchy solve your problem? It should be the right most hirearchy control on the visual and looks like an arrow forking into two.
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.
Proud to be a Super User!
Paul on Linkedin.
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?
It all depends what you are trying to portray. The hybrid table can be useful, but if can also be detrimental to performance.
You can avchieve something similar to the structure you posted using the default table visual. For example:
To get total columns in a "hybrid" matrix structure, you need to build in the columns into the actual Hybrid table structure:
Values for Matrix =
VAR _Val =
SWITCH (
SELECTEDVALUE ( 'Hybrid Table'[INDEX] ),
1, [Total Sales],
2, [Total Target],
3, [Total Sales] + [Total Target],
4, [Total Budget],
5, [Total Forecast],
6, [Total Budget] + [Total Forecast],
7,
[Total Sales] + [Total Target] + [Total Budget] + [Total Forecast]
)
RETURN
_Val
To add conditional formatting, create measure for colour codes and text:
Colour Code Full =
VAR _CC =
SWITCH (
TRUE (),
SELECTEDVALUE ( 'Hybrid Table'[INDEX] ) = 1, "#99d6ff",
SELECTEDVALUE ( 'Hybrid Table'[INDEX] ) = 2, "#4d79ff",
AND (
SELECTEDVALUE ( 'Hybrid Table'[INDEX] ) = 3,
[Total Sales] + [Total Target] > 1000
), "#339933",
SELECTEDVALUE ( 'Hybrid Table'[INDEX] ) = 3, "#b3e6b3",
SELECTEDVALUE ( 'Hybrid Table'[INDEX] ) = 4, "#ff66d9",
SELECTEDVALUE ( 'Hybrid Table'[INDEX] ) = 5, "#ff1a8c",
AND (
SELECTEDVALUE ( 'Hybrid Table'[INDEX] ) = 6,
[Total Budget] + [Total Forecast] > 1000
), "#ff0000",
SELECTEDVALUE ( 'Hybrid Table'[INDEX] ) = 6, "#ff9933",
AND (
SELECTEDVALUE ( 'Hybrid Table'[INDEX] ) = 7,
[Total Sales] + [Total Target] + [Total Budget] + [Total Forecast] > 2000
), "#8c1aff",
SELECTEDVALUE ( 'Hybrid Table'[INDEX] ) = 7, "#bf80ff"
)
RETURN
_CC
TEXT Code Full =
VAR _CC =
SWITCH (
TRUE (),
SELECTEDVALUE ( 'Hybrid Table'[INDEX] ) = 2, "White",
AND (
SELECTEDVALUE ( 'Hybrid Table'[INDEX] ) = 3,
[Total Sales] + [Total Target] > 1000
), "White",
SELECTEDVALUE ( 'Hybrid Table'[INDEX] ) = 5, "White",
AND (
SELECTEDVALUE ( 'Hybrid Table'[INDEX] ) = 6,
[Total Budget] + [Total Forecast] > 1000
), "White",
AND (
SELECTEDVALUE ( 'Hybrid Table'[INDEX] ) = 7,
[Total Sales] + [Total Target] + [Total Budget] + [Total Forecast] > 2000
), "White",
"Black"
)
RETURN
_CC
To get rid of the "GROUPING", simply rename the column blank (highlighted in above image).
I've attached the sample PBIX file
Proud to be a Super User!
Paul on Linkedin.
@PaulDBrownI have used the method you mentioned,
but the values are shown in same format, all two decimals number, but I want some of them as whole number, decimals and percentages.
How to achieve that?
You need to use the FORMAT function to establish the format for each type of values.
You can see an example of this in this article:
Proud to be a Super User!
Paul on Linkedin.
How would you be able to sort the the rows by one of the column names in the group ? For example sort "Country" column on the rows based on descending order of "Total Actuals" which would put "UK" as the first row in the matrix ?
@PaulDBrown Any ideas ? or I assume since you haven't replied it might not be possible.
One way is to create a table with the measure names. See if this helps:
Creating a custom or hybrid matrix
Proud to be a Super User!
Paul on Linkedin.
Thanks for forwarding the artcile. Very good insight. However, in my case, I need to group measures under two sections in the columns header and also be able to conditionally format values in each column/measure independently.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
146 | |
75 | |
64 | |
52 | |
47 |
User | Count |
---|---|
218 | |
87 | |
71 | |
63 | |
60 |