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...

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.


      • BIswajit_Das's avatar
        BIswajit_Das
        Impactful Individual
        2 years ago

        Hello Fowmy 
        I want to Calculate the percentage of MAX value and the 2ND MAX value
        Then I want to calculated their Percentage Gap
        Then I want to Group them by 0-5%, 6-10%, 11-15%
        And In the End I want to Check The Max Item And the Group and Apply color according to the Results..