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
johnyip's avatar
johnyip
Solution Sage
2 years ago

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.