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

[Anonymous]

Hi,

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

Sample Table Data

Region	Location	Item	Volume
Region1	Location4	Item2	2968
Region1	Location4	Item2	44
Region1	Location4	Item1	163
Region1	Location4	Item1	44
Region1	Location4	Item7	1043
Region1	Location4	Item7	55
Region1	Location2	Item4	20190
Region1	Location2	Item4	44
Region1	Location3	Item2	127
Region1	Location3	Item2	44
Region1	Location3	Item1	0
Region1	Location3	Item1	44
Region1	Location3	Item9	73
Region1	Location3	Item9	44
Region1	Location3	Item4	222
Region1	Location3	Item4	44
Region1	Location3	Item3	389
Region1	Location3	Item3	44
Region1	Location3	Item7	86
Region1	Location3	Item7	44
Region1	Location3	Item6	0
Region1	Location3	Item6	44
Region1	Location3	Item8	1085
Region1	Location3	Item8	44
Region1	Location1	Item9	24
Region1	Location1	Item9	44
Region1	Location1	Item8	96
Region1	Location1	Item8	44
Region1	Location5	Item9	1
Region1	Location5	Item9	55
Region1	Location5	Item6	9
Region1	Location5	Item8	30
Region1	Location5	Item8	55
Region1	Location7	Item2	46
Region1	Location7	Item2	55
Region1	Location7	Item9	25
Region1	Location7	Item9	66
Region1	Location7	Item4	112
Region1	Location7	Item4	55
Region1	Location7	Item3	139
Region1	Location7	Item3	55
Region1	Location7	Item7	63
Region1	Location7	Item7	55
Region1	Location7	Item8	431
Region1	Location7	Item8	66
Region1	Location7	Item5	NULL
Region1	Location6	Item4	4373
Region1	Location6	Item4	55
Region2	Location12	Item4	358
Region2	Location12	Item4	NULL
Region2	Location10	Item5	NULL
Region2	Location10	Item5	NULL
Region2	Location9	Item4	581
Region2	Location9	Item4	NULL
Region2	Location11	Item4	1270
Region2	Location11	Item4	NULL
Region2	Location8	Item2	NULL
Region2	Location8	Item2	NULL

Matrix Visual

   

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.

[Anonymous · Accepted solution]

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

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

Thank you.

[Anonymous]

Hi, 

Could you please check below link where I have updated how to created DAX for cell background color 

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Power-BI-Conditional-Formatting-to-Table-cell...

it's my first post, please check it will be helpful to you.

Thanks

Shashi Mudhe

[TomMartens]

Hey,

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

Regards,

Tom

[Anonymous]

Thank you Tom.

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

  Thanks.

[v-chuncz-msft]

@Anonymous,

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.

[Anonymous · Accepted solution]

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

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

Thank you.

[Anonymous · Accepted solution]

Hi,  Please refer the below link for the solution: 

https://community.powerbi.com/t5/Desktop/Table-visual-conditional-formatting-to-set-background-color...