Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 me...
Need Help
Show and Tell
Tips and Tricks
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    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.

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
    PaulDBrown
    Community Champion
    4 years ago

    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.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        PaulDBrown
        Community Champion
        4 years ago

        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