Power BI - Matrix : Column Header Grouping for multiple measures

[ippman]

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?

[Dimitris_Kats]

Hi. Great solution. Is there any other way to achieve the same results without switch? 

[v-yalanwu-msft · Accepted 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.

[theonexcent]

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?

[add]

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.

[ippman]

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

[PaulDBrown]

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.

[ippman]

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?

[PaulDBrown]

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

[RahulNadkarni]

Great solution. Perfectly work for case my client want the measures to be grouped under some criteria. Only issue is that some columns are numeric in Millian and some are in %.

It there any way we can format these individual mesures.

[PaulDBrown]

Sure. Just wrap the measures you need to format differently in the FORMAT function with the necessary format type

[RahulNadkarni]

Thanks.

I need to change color of a column based on below condition for a percentage field

1) if between -2 nd 2 then green

2) for rest cases red

But it's changing all values to Green inspite of check condition

The mesure is of type percentage.

Please let me know in case any solution.

TEXT Code for MainReport =

VAR _CC =

    SWITCH (

        TRUE (),

        AND (

            SELECTEDVALUE ( 'MainReport'[INDEX] ) = 15 ,          

            [UTILIZATION%] > 2

        ), "red",

        AND (

            SELECTEDVALUE ( 'MainReport'[INDEX] ) = 15 ,         

            AND([UTILIZATION%] <= 2 ,

            [UTILIZATION%] >= -2)

        ), "green",

        AND (

            SELECTEDVALUE ( 'MainReport'[INDEX] ) = 15 ,          

            [UTILIZATION%] < -2

        ), "#ff0000"

    )

RETURN

    _CC

[ddpl]

@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?

[PaulDBrown]

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:

https://community.fabric.microsoft.com/t5/Community-Blog/Creating-a-custom-or-hybrid-matrix-in-Power... 

[oscarca]

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  ?

[oscarca]

@PaulDBrown Any ideas ? or I assume since you haven't replied it might not be possible.

[PaulDBrown]

One way is to create a table with the measure names. See if this helps:

Creating a custom or hybrid matrix 

[ippman]

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.