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