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 | 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..
16 Replies
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 ?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 colorWhat if same values are found for MAX or 2nd MAX ?
Take it as draw or blank i guessThanks & 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
I created a measure for Conditional Formatting called CF. I also made a table to pick the colors for items with colors ranging from 1 to 10. You may modify this table as necessary. Here is the measure.
File is attached below.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)Hello Fowmy Thanks for the content
but
I want to color according to the Gap Group but when I am modifing the content it's not working
Using DAX :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 - __Max2VAR _gapgroup = IF(__Gap >= 0 && __Gap <= 0.05,"0-5%",IF(__Gap > 0.05 && __Gap <= 0.1,"6-10%",""))// VAR _check = IF(_gapgroup = "0-5%",1,IF(_gapgroup = "6-10%",2,0))VAR __Color = LOOKUPVALUE( Colors[Color] , Colors[Item] , __Item , Colors[GROUP] , _gapgroup )RETURNIF( [Count] = __Max1 , __Color)
