cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
v_krishna Member
Member

Conditional formatting to set background color to the column values in Matrix visual

Hi,

 

Based on below Sample Table Data, I have tried and able to display the data in Matrix visual as below.

 

Sample Table Data

          

RegionLocationItemVolume
Region1Location4Item22968
Region1Location4Item244
Region1Location4Item1163
Region1Location4Item144
Region1Location4Item71043
Region1Location4Item755
Region1Location2Item420190
Region1Location2Item444
Region1Location3Item2127
Region1Location3Item244
Region1Location3Item10
Region1Location3Item144
Region1Location3Item973
Region1Location3Item944
Region1Location3Item4222
Region1Location3Item444
Region1Location3Item3389
Region1Location3Item344
Region1Location3Item786
Region1Location3Item744
Region1Location3Item60
Region1Location3Item644
Region1Location3Item81085
Region1Location3Item844
Region1Location1Item924
Region1Location1Item944
Region1Location1Item896
Region1Location1Item844
Region1Location5Item91
Region1Location5Item955
Region1Location5Item69
Region1Location5Item830
Region1Location5Item855
Region1Location7Item246
Region1Location7Item255
Region1Location7Item925
Region1Location7Item966
Region1Location7Item4112
Region1Location7Item455
Region1Location7Item3139
Region1Location7Item355
Region1Location7Item763
Region1Location7Item755
Region1Location7Item8431
Region1Location7Item866
Region1Location7Item5NULL
Region1Location6Item44373
Region1Location6Item455
Region2Location12Item4358
Region2Location12Item4NULL
Region2Location10Item5NULL
Region2Location10Item5NULL
Region2Location9Item4581
Region2Location9Item4NULL
Region2Location11Item41270
Region2Location11Item4NULL
Region2Location8Item2NULL
Region2Location8Item2NULL

 

 

Matrix Visual

 

   RegionMatrixCapture.PNG

 

 

Need help and inputs:   how to add conditional formatting  to  set some background color to the Region column values ( Region1 & Region2 ) 

 

I have tried below options but not able to get  to the solution.  please advise.

 

1)  I have written a measure to detemine the color rule but  i am not sure how to set the conditional formatting for background color .

 

ColorMeasure =
IF (
MAX ( [RegionName] ) = "Region1", 1,
IF ( MAX ( [RegionName] ) = "Region2", 2, 0 )
)

 

2)  I have create a conditional column in Power Query but again as per my understanding this also requires to set the conditional formatting as well for background color.

 

 

 

Thanks.

2 ACCEPTED SOLUTIONS

Accepted Solutions
v_krishna Member
Member

Re: Conditional formatting to set background color to the column values in Matrix visual

Thank you for the suggestion. I did submit the idea in powerbi forums. 

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/36010816-conditional-formatting-t...

 

For reference, i have tried a work around:

 

Instead of Matrix visual, i have used Table visual to apply Conditional formatting to set background color to the column values. 

 

Below are steps  and refer Image 1:

 

1. Used Table visual. 

 

2. Created below columns (using New Column option) in RegionBreakdown table.
 
 VolumeCheck = if(((RegionBreakdown[Volume]=0) || (RegionBreakdown[Volume]=BLANK())),BLANK(), RegionBreakdown[Volume])
 
 Item1 = If(RegionBreakdown[ItemName] = "Item1", RegionBreakdown[VolumeCheck], 0)
 
 RegionColor = if( RegionBreakdown[RegionName]="Region1", 1 ,2 )
 
3.  Table Fileds  -  dragged RegionName, LocationName, Item1  to the Values.
 
4.  on RegionName Value -  i have applied Conditional formatting to set background color to the  RegionName column values using   RegionColor  condition.
       

Image 1

 

tableCapture1.PNG

 

 

I am facing an issue for below Requirement . Need help to resolve the issue.

Requirement:   when Item1 volume value is   0  or  empty,  display blank.   Refer my inital post in this thread for Sample Table Data. 

 

I have update column condition for item1.   Refer the Image 2

i.e.  Item1 = If(RegionBreakdown[ItemName] = "Item1", RegionBreakdown[VolumeCheck], BLANK())

 

 

Issue : When the Item1 value is blank/empty, the background color is not applied to the Region. The background color is only set to the RegionNames with value in Item1. 

 

 

Image 2

table1Capture.PNG

 

 

Thank you.

v_krishna Member
Member

Re: Conditional formatting to set background color to the column values in Matrix visual

5 REPLIES 5
Super User
Super User

Re: Conditional formatting to set background color to the column values in Matrix visual

Hey,

 

can you please upload a "faked" image, how you want your matrix visual to look like.

 

Regards,

Tom

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
v_krishna Member
Member

Re: Conditional formatting to set background color to the column values in Matrix visual

Thank you Tom.

 

Below is the expected  "faked" image Matrix visual for reference.

 

expectedBackgroundcolorCapture.PNG

 

 

  Thanks.

 

 

Community Support Team
Community Support Team

Re: Conditional formatting to set background color to the column values in Matrix visual

@v_krishna,

 

You can only manage conditional formatting for fields in the Values area. To help improve Power BI, you may submit an idea via https://ideas.powerbi.com/forums/265200-power-bi.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v_krishna Member
Member

Re: Conditional formatting to set background color to the column values in Matrix visual

Thank you for the suggestion. I did submit the idea in powerbi forums. 

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/36010816-conditional-formatting-t...

 

For reference, i have tried a work around:

 

Instead of Matrix visual, i have used Table visual to apply Conditional formatting to set background color to the column values. 

 

Below are steps  and refer Image 1:

 

1. Used Table visual. 

 

2. Created below columns (using New Column option) in RegionBreakdown table.
 
 VolumeCheck = if(((RegionBreakdown[Volume]=0) || (RegionBreakdown[Volume]=BLANK())),BLANK(), RegionBreakdown[Volume])
 
 Item1 = If(RegionBreakdown[ItemName] = "Item1", RegionBreakdown[VolumeCheck], 0)
 
 RegionColor = if( RegionBreakdown[RegionName]="Region1", 1 ,2 )
 
3.  Table Fileds  -  dragged RegionName, LocationName, Item1  to the Values.
 
4.  on RegionName Value -  i have applied Conditional formatting to set background color to the  RegionName column values using   RegionColor  condition.
       

Image 1

 

tableCapture1.PNG

 

 

I am facing an issue for below Requirement . Need help to resolve the issue.

Requirement:   when Item1 volume value is   0  or  empty,  display blank.   Refer my inital post in this thread for Sample Table Data. 

 

I have update column condition for item1.   Refer the Image 2

i.e.  Item1 = If(RegionBreakdown[ItemName] = "Item1", RegionBreakdown[VolumeCheck], BLANK())

 

 

Issue : When the Item1 value is blank/empty, the background color is not applied to the Region. The background color is only set to the RegionNames with value in Item1. 

 

 

Image 2

table1Capture.PNG

 

 

Thank you.

v_krishna Member
Member

Re: Conditional formatting to set background color to the column values in Matrix visual