Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
BIswajit_Das
Resolver III
Resolver III

Power BI Matrix

Hello,
I have a table with data

SLNOITEMPLACE_CODE
1A100
1A101
1A102
1A103
1A104
1B105
1B106
1D107
1C108
1C109
1C110
2D111
2D112
2A113
2A114
2A115
2C116
2C117
2C118
2B119
2A120
2B121
3C122
3C123
3C124
3A125
3A126
3D127
3D128

and I am using matrix visual to visualize the data like

SLNOABCD
15231
24232
32 32

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
sa.png

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 16
BIswajit_Das
Resolver III
Resolver III

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.

johnyip_0-1703597471519.png

 

4. Apply the below conditional formatting rule to [Measure_Item].

johnyip_0-1703761646219.png

 

 

After hiding the subtotals, you would have the below matrix.

johnyip_1-1703761671585.png

 



Please mark my post as the solution if this answers your question!
Appreciate your Kudos !!

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.

johnyip_1-1703763176293.png

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.



Please mark my post as the solution if this answers your question!
Appreciate your Kudos !!

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...

@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?



Please mark my post as the solution if this answers your question!
Appreciate your Kudos !!

Hello @johnyip 
Here's the

COLOR_TABLE :

ItemCodeColor
A0-5#fcdc9c
A6-10#fdcf63
A11-15#f6a028
A16-20#fa9202
A21-30#ac5a02
AABOVE 31#753d01
B0-5#9df2d3
B6-10#66edbc
B11-15#42ffba
B16-20#0fd68e
B21-30#08754e
BABOVE 31#086644
C0-5#fabee0
C6-10#e090bd
C11-15#e472ed
C16-20#a757ad
C21-30#78367d
CABOVE 31#45024a
D0-5NA
D6-10NA
D11-15NA
D16-20NA
D21-30NA
DABOVE 31NA

DATA :

SLNOITEMPLACE_CODE
1A100
1A101
1A102
1A103
1A104
1B105
1B106
1D107
1C108
1C109
1C110
2D111
2D112
2A113
2A114
2A115
2C116
2C117
2C118
2B119
2A120
2B121
3C122
3C123
3C124
3A125
3A126
3D127
3D128
4A129
4A130
4A131
4A132
4A133
4B134
4B135
4C136

If you check you'll find that the colors are not reflecting based on the gap group value.

@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)

Fowmy_0-1703621275256.png

 




Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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 - __Max2
VAR _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 )
RETURN
     IF( [Count] = __Max1 , __Color)
  

@BIswajit_Das 

Sorry, I didn't understand the issue that you are facing. Please elaborate.


Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Hello @Fowmy 
I want to Calculate the percentage of MAX value and the 2ND MAX value
Then I want to calculated their Percentage Gap
Then I want to Group them by 0-5%, 6-10%, 11-15%
And In the End I want to Check The Max Item And the Group and Apply color according to the Results..

@BIswajit_Das 

Your initial inquiry did not align with this requirement, as I understand it. The solution I presented was tailored to the information provided in the original question. Given that it meets the expectations for that specific query, I recommend accepting my response as the solution. For any further inquiries, please initiate a new question with a clear explanation and desired outcome for a more accurate response.

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

johnyip
Super User
Super User

@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.

johnyip_0-1703597471519.png

 

4. Apply the below conditional formatting rule to [Measure_Item].

johnyip_1-1703597520772.png

 

After hiding the subtotals, you would have the below matrix.

johnyip_2-1703597568638.png

 

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.



Please mark my post as the solution if this answers your question!
Appreciate your Kudos !!
Fowmy
Super User
Super User

@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 ?




Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Analytics1
Frequent Visitor

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors