Forum Discussion
Power BI Matrix
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...
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.
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.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.
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)RETURNIF( [@EP_RECORDS] = _Max1 , _Color)
It's not working
if possible can you modify it.
Thanks & Regards...