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 ...
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...
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?
Hello johnyip
Here's theCOLOR_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 If you check you'll find that the colors are not reflecting based on the gap group value.