Forum Discussion
Power BI - Matrix : Column Header Grouping for multiple measures
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.
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
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.