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

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

Hi,

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

          

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

 

 
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

 

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

table1Capture.PNG

 

 

Thank you.

2 ACCEPTED SOLUTIONS

Accepted Solutions
v-cherch-msft Super Contributor
Super Contributor

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

Hi @v_krishna

 

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 )

1.png

 

Regards,

Cherie

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

View solution in original post

v_krishna Member
Member

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

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 )

 

View solution in original post

3 REPLIES 3
v-cherch-msft Super Contributor
Super Contributor

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

Hi @v_krishna

 

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 )

1.png

 

Regards,

Cherie

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

View solution in original post

v_krishna Member
Member

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

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 )

 

View solution in original post

Highlighted
v_krishna Member
Member

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

Hi Cherie,

 

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

 

 

 GroupByCapture.PNG

 

Thank you.

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 154 members 1,719 guests
Please welcome our newest community members: