Power BI Matrix

[BIswajit_Das]

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	110
2	D	111
2	D	112
2	A	113
2	A	114
2	A	115
2	C	116
2	C	117
2	C	118
2	B	119
2	A	120
2	B	121
3	C	122
3	C	123
3	C	124
3	A	125
3	A	126
3	D	127
3	D	128

and I am using matrix visual to visualize the data like

SLNO	A	B	C	D
1	5	2	3	1
2	4	2	3	2
3	2		3	2

and as per my requirements I want use colors to set on max value and  the gap between max and 2nd max value
i.e

Like 
In SLNO-1
5 is large and 3 is second large
So Gap = 5-3  = 2 -> Color - Dark Orange
In SLNO -2
4 is large and 3 is second large

so Gap = 4-3 = 1 -> Color - Light Orange
Like wise....
Is there any way to acheive this 

Thanks & Regards..

[BIswajit_Das]

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]

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

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]

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

[BIswajit_Das]

Hello @johnyip it is working but when I am modifing the DAX like

@EP_PPD_COLOR =

VAR _Item = SELECTEDVALUE( 'Z_EP_DATA'[RESPONSE1] )

VAR _T = ADDCOLUMNS( ALLSELECTED( 'Z_EP_DATA'[RESPONSE1] ) , "@Max" , [@EP_RECORDS] )

VAR _Max1 = MAXX( _T , [@Max] )

VAR _Max2 =  MAXX( FILTER( _T , [@Max] < _Max1 ) , [@Max] )

VAR _GapPercent = (DIVIDE((_Max1 - _Max2),[@EP_RECORDS]))

VAR _Gap = SWITCH(TRUE(),_GapPercent > 0.3,"ABOVE 31",_GapPercent > 0.2,"21-30",_GapPercent > 0.15,"16-20",_GapPercent > 0.10,"11-15",_GapPercent > 0.05,"6-10",_GapPercent > 0,"0-5")

VAR _Color = LOOKUPVALUE('PARTY_COLORS'[COLOR],PARTY_COLORS[RESPONSE],_Item,PARTY_COLORS[MARGIN],_Gap)

RETURN

     IF( [@EP_RECORDS] = _Max1 , _Color)
It's not working
if possible can you modify it.
Thanks & Regards...

[johnyip]

@BIswajit_Das , as I have mentioned in my previous answer, you should also add additional rows in the color table. Specifically, from your DAX, it is "16-20" ,"21-30", "ABOVE 31", as well as modifying "11-" to "11-15". You should also add the color code for each new rows (where that color will be used for that criteria).

And I saw in the DAX you provided has some new measures, such as [@EP_RECORDS], and aslo some table different from the provided asnwer, like PARTY_COLORS.

Can you provide your sample data here for checking?

[BIswajit_Das]

Hello @johnyip 
Here's the

COLOR_TABLE :

Item	Code	Color
A	0-5	#fcdc9c
A	6-10	#fdcf63
A	11-15	#f6a028
A	16-20	#fa9202
A	21-30	#ac5a02
A	ABOVE 31	#753d01
B	0-5	#9df2d3
B	6-10	#66edbc
B	11-15	#42ffba
B	16-20	#0fd68e
B	21-30	#08754e
B	ABOVE 31	#086644
C	0-5	#fabee0
C	6-10	#e090bd
C	11-15	#e472ed
C	16-20	#a757ad
C	21-30	#78367d
C	ABOVE 31	#45024a
D	0-5	NA
D	6-10	NA
D	11-15	NA
D	16-20	NA
D	21-30	NA
D	ABOVE 31	NA

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	110
2	D	111
2	D	112
2	A	113
2	A	114
2	A	115
2	C	116
2	C	117
2	C	118
2	B	119
2	A	120
2	B	121
3	C	122
3	C	123
3	C	124
3	A	125
3	A	126
3	D	127
3	D	128
4	A	129
4	A	130
4	A	131
4	A	132
4	A	133
4	B	134
4	B	135
4	C	136

[BIswajit_Das]

If you check you'll find that the colors are not reflecting based on the gap group value.

[Fowmy]

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

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]

@BIswajit_Das 

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

[BIswajit_Das]

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

[Fowmy]

@BIswajit_Das 

Your initial inquiry did not align with this requirement, as I understand it. The solution I presented was tailored to the information provided in the original question. Given that it meets the expectations for that specific query, I recommend accepting my response as the solution. For any further inquiries, please initiate a new question with a clear explanation and desired outcome for a more accurate response.

[johnyip]

@BIswajit_Das , based on your sample data, I come up with the following.

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

I am not sure why you are showing green for SLNO 3 Item C. You can modify the code in [Gap] or change the conditional formatting setting to cater to your requirement.

[Fowmy]

@BIswajit_Das 

What if the gap is not 1 or 2 ?
What does green color in your example show?
What if same values are found for MAX or 2nd MAX ?

[Analytics1]

I dont think there is a way to do this directly on the current matrix visual. You can try using charticulator to create a custom matrix visual. You can set the background color of each "cell" using a formula.