Table visual - conditional formatting to set background color to the column values

[Anonymous]

Hi,

Based on below Sample Table Data, I have tried and able to display the data in Table visual. Refer Image 1

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

 
i have applied 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 Fields  -  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/NULL,  then display blank.   

I have update column condition for item1.   

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. Refer the Image 2

Image 2

Thank you.

[v-cherch-msft · Accepted solution]

Hi @Anonymous

You may try to create a measure for RegionColor. Then set the conditional formatting with the measure.

RegionColorMeasure = if( MAX(RegionBreakdown[RegionName])="Region1", 1 ,2 )

Regards,

Cherie

[v-cherch-msft · Accepted solution]

Hi @Anonymous

You may try to create a measure for RegionColor. Then set the conditional formatting with the measure.

RegionColorMeasure = if( MAX(RegionBreakdown[RegionName])="Region1", 1 ,2 )

Regards,

Cherie

[Anonymous]

Hi Cherie,

Can we groupby RegionName in Table visual something like below ?. Refer the attached image visual.

 

Thank you.

[Anonymous · Accepted solution]

Thank you for correcting the mistake i did. I wrote the measure as below

RegionColor = if( RegionBreakdown[RegionName]="Region1", 1 ,2 )

BUT it should be written as 

RegionColor = if( MAX(RegionBreakdown[RegionName])="Region1", 1 ,2 )