Hello,I have a table with dataSLNOITEMPLACE_CODE1A1001A1011A1021A1031A1041B1051B1061D1071C1081C1091C1102D1112D1122A1132A1142A1152C1162C1172C1182B1192A1202B1213C1223C1233C1243A1253A1263D1273D128and I am using matrix visual to visualize the data likeSLNOABCD152312423232 32and as per my requirements I want use colors to set on max value and the gap between max and 2nd max valuei.eLike In SLNO-15 is large and 3 is second largeSo Gap = 5-3 = 2 -> Color - Dark OrangeIn SLNO -24 is large and 3 is second largeso Gap = 4-3 = 1 -> Color - Light OrangeLike wise....Is there any way to acheive this Thanks & Regards..

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.

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.

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 ?

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

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

Need Help

Forum Discussion

Power BI Matrix

Related content

Power BI Matrix

Matrix table in Power bi

Power BI Matrix report

Keeping Power BI Matrix Expanded

Power BI matrix query