Forum Discussion

BIswajit_Das's avatar
BIswajit_Das
Impactful Individual
2 years ago

Power BI Matrix

Hello, I have a table with data SLNO ITEM PLACE_CODE 1 A 100 1 A 101 1 A 102 1 A 103 1 A 104 1 B 105 1 B 106 1 D 107 1 C 108 1 C 109 1 C ...
Need Help
BIswajit_Das's avatar
BIswajit_Das
Impactful Individual
2 years ago

Hello Fowmy , johnyip , Analytics1 
Thanks for responding
As per the requirement 
each item have spefic colors
i.e for A its dark to light orange based on the gap
and For C its Dark green to light green.
What if the gap is not 1 or 2 ?
Lets take a mid val i.e 5 and Gap >5 color dark and Gap < 5 Light 
What does green color in your example show?
As I've written Each Item have Specific color

What if same values are found for MAX or 2nd MAX ?
Take it as draw or blank i guess

Thanks & Regards...

  • johnyip's avatar
    johnyip
    Solution Sage
    2 years ago

    BIswajit_Das , is this what you are looking for?

     

    1. Create the below measure.

     

    Measure_Item = COUNT('Table'[ITEM])

     

     

     

    2. Create the below measure.

     

    Gap = 
VAR A = CALCULATE(COUNTX(FILTER('Table','Table'[ITEM]="A"),'Table'[ITEM]),REMOVEFILTERS('Table'[ITEM]))
VAR B = CALCULATE(COUNTX(FILTER('Table','Table'[ITEM]="B"),'Table'[ITEM]),REMOVEFILTERS('Table'[ITEM]))
VAR C = CALCULATE(COUNTX(FILTER('Table','Table'[ITEM]="C"),'Table'[ITEM]),REMOVEFILTERS('Table'[ITEM]))
VAR D = CALCULATE(COUNTX(FILTER('Table','Table'[ITEM]="D"),'Table'[ITEM]),REMOVEFILTERS('Table'[ITEM]))
VAR MAX1 = MAX(MAX(MAX(A,B),C),D)
VAR MAX2 = MINX(TOPN(2,SELECTCOLUMNS({A,B,C,D},"Value",[Value]),[Value],DESC),[Value])
RETURN 
IF([Measure_Item]=MAX1,MAX1-MAX2)

     

     

     

    3.Create the below measure

     

    Conditional formating = 
VAR i = SWITCH(MAX('Table'[Item]),
                  "A",0.1,
                  "B",0.2,
                  "C",0.3,
                  "D",0.4
            )
VAR Result = [Gap]+i
RETURN
SWITCH(Result,
       1.1, "#FFCB8F", // A: light orange (gap=1)
       2.1, "#FF7F27", // A: orange (gap=2)
       1.2, "#FFFEAA", // B: light yellow (gap=1)
       2.2, "#FFF200", // B: Yellow (gap=2)
       1.3, "#B5E61D", // C: light green (gap=1)
       2.3, "#22B14C", // C: green (gap=2)
       1.4, "#99D9EA", // 😧 light blue (gap=1)
       2.4, "#00A2E8" //  😧 blue (gap=2)
       )

     

     

     

    4. Build your matrix as follow.

     

    4. Apply the below conditional formatting rule to [Measure_Item].

     

     

    After hiding the subtotals, you would have the below matrix.

     

    • BIswajit_Das's avatar
      BIswajit_Das
      Impactful Individual
      2 years ago

      Hello johnyip  not really

      Let's take your previous query

      CF = 
VAR __Item = SELECTEDVALUE( 'Table'[ITEM] )
VAR __T = ADDCOLUMNS( ALLSELECTED( 'Table'[ITEM] ) , "@Max" , [Count] )
VAR __Max1 = MAXX( __T , [@Max] )
VAR __Max2 =  MAXX( FILTER( __T , [@Max] < __Max1 ) , [@Max] )
VAR __Gap = __Max1 - __Max2
VAR __Color = LOOKUPVALUE( Colors[Color] , Colors[Item] , __Item , Colors[Code] , __Gap )
RETURN
     IF( [Count] = __Max1 , __Color)

      This is working fine with count values 
      But as per my requirement i need percentage values
      Then need to group them 
      then Create the Mentioned Matrix and 
      Add Background on it based on the MAX% VAL and Gap_Group.

      • johnyip's avatar
        johnyip
        Solution Sage
        2 years ago

        BIswajit_Das , based on your requirements, I worked on the file provided by Fowmy .

         

        I made the below changes.

         

        1. The color table as below.

        I removed some extra rows, retaining only 3 rows per item, as per your requirement of "0-5%", "6-10%", "11- (more) %"

         

        2. I modified the [CF] measure.

         

         

        CF = 
VAR __Item = SELECTEDVALUE( 'Table'[ITEM] )
VAR __T = ADDCOLUMNS( ALLSELECTED( 'Table'[ITEM] ) , "@Max" , [Count] )
VAR __Max1 = MAXX( __T , [@Max] )
VAR __Max2 =  MAXX( FILTER( __T , [@Max] < __Max1 ) , [@Max] )
--------------------------------------------------------------------------------------
VAR __GapPercent = ABS(DIVIDE((__Max1 - __Max2),__Max2))
VAR __Gap = SWITCH(TRUE(),__GapPercent <=0.05,"0-5",__GapPercent <=0.1,"6-10","11-")
--------------------------------------------------------------------------------------
VAR __Color = LOOKUPVALUE( Colors[Color] , Colors[Item] , __Item , Colors[Code] , __Gap )
RETURN 
     IF( [Count] = __Max1 , __Color)

         

         

         

        And there you are.

         

        In case you need to add more layers of coloring based on percentage, you should add more rows in the color table, and make changes to __Gap in the measure within the SWITCH() structure, as __GapPercent <= upper bound. Make sure the the upper bound with lower value is placed first within the SWITCH() construct.

  • Fowmy's avatar
    Fowmy
    Super User
    2 years ago

    BIswajit_Das 

    I created a measure for Conditional Formatting called CF. I also made a table to pick the colors for items with colors ranging from 1 to 10. You may modify this table as necessary. Here is the measure.

    File is attached below.

    CF = 
VAR __Item = SELECTEDVALUE( 'Table'[ITEM] )
VAR __T = ADDCOLUMNS( ALLSELECTED( 'Table'[ITEM] ) , "@Max" , [Count] )
VAR __Max1 = MAXX( __T , [@Max] )
VAR __Max2 =  MAXX( FILTER( __T , [@Max] < __Max1 ) , [@Max] )
VAR __Gap = __Max1 - __Max2
VAR __Color = LOOKUPVALUE( Colors[Color] , Colors[Item] , __Item , Colors[Code] , __Gap )
RETURN
     IF( [Count] = __Max1 , __Color)

     




    • BIswajit_Das's avatar
      BIswajit_Das
      Impactful Individual
      2 years ago

      Hello Fowmy Thanks for the content
      but
      I want to color according to the Gap Group but when I am modifing the content it's not working
      Using DAX :

      CF =
      VAR __Item = SELECTEDVALUE( 'Table'[ITEM] )
      VAR __T = ADDCOLUMNS( ALLSELECTED( 'Table'[ITEM] ) , "@Max" , [Count] )
      VAR __Max1 = MAXX( __T , [@Max] )
      VAR __Max2 =  MAXX( FILTER( __T , [@Max] < __Max1 ) , [@Max] )
      VAR __Gap = __Max1 - __Max2
      VAR _gapgroup = IF(__Gap >= 0 && __Gap <= 0.05,"0-5%",IF(__Gap > 0.05 && __Gap <= 0.1,"6-10%",""))
      // VAR _check = IF(_gapgroup = "0-5%",1,IF(_gapgroup = "6-10%",2,0))
      VAR __Color = LOOKUPVALUE( Colors[Color] , Colors[Item] , __Item , Colors[GROUP] , _gapgroup )
      RETURN
           IF( [Count] = __Max1 , __Color)
        
      • Fowmy's avatar
        Fowmy
        Super User
        2 years ago

        BIswajit_Das 

        Sorry, I didn't understand the issue that you are facing. Please elaborate.