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.

     

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

 

 

 

RahulNadkarni's avatar
RahulNadkarni
Frequent Visitor
1 year ago

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's avatar
    PaulDBrown
    Community Champion
    1 year ago

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

    • RahulNadkarni's avatar
      RahulNadkarni
      Frequent Visitor
      1 year ago

      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