Forum Discussion
Conditional Formatting in Table
Hi,
I have a set of data similar to the table below that I am looking to provide conditional formatting on. However I would like this to be linked to the Region, so for example the conditional formatting for North only looks at 34.2, 33.8, 32.7 and applies the colour gradient to those values. The same conditional formatting is then applied for East, West, etc.
Is this possible whilst keeping all the percentages in the same column?
Thank you
| Region | % From World Average |
| North | 34.2% |
| North | 33.8% |
| North | 32.7% |
| East | 12.1% |
| East | 16.5% |
| East | 8.4% |
| West | 66.1% |
| West | 62.9% |
Hi,
According to your description, I can roughly understand your requirement, you want to apply the color gradient on the conditional formatting for each region within your dataset, right? I think you can try this method:
First, create a calculated column in the table like this:
Rank = RANKX(FILTER(ALL('Table'),[Region]=EARLIER('Table'[Region])),[% From World Average],,ASC,Dense)Then you can create a measure like this:
Color = SWITCH( MAX('Table'[Rank]), 1,"White", 2,"Light Blue", 3,"Blue")Then go to the conditional formatting setting of this chart and set it like this:
And you can get what you want, like this:
You can download my test pbix file below
If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.
How to Get Your Question Answered Quickly
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Hi,
According to your description, I can roughly understand your requirement, you want to apply the color gradient on the conditional formatting for each region within your dataset, right? I think you can try this method:
First, create a calculated column in the table like this:
Rank = RANKX(FILTER(ALL('Table'),[Region]=EARLIER('Table'[Region])),[% From World Average],,ASC,Dense)Then you can create a measure like this:
Color = SWITCH( MAX('Table'[Rank]), 1,"White", 2,"Light Blue", 3,"Blue")Then go to the conditional formatting setting of this chart and set it like this:
And you can get what you want, like this:
You can download my test pbix file below
If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.
How to Get Your Question Answered Quickly
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you, I am just looking into transposing this onto my datafile
Hi, tomshaw83
Has my reply helped you to find the solution to this problem? If so, would you like to mark my reply as the solution?
If this result is not what you want, you can post the download link of your data file and your expected result.
How to Get Your Question Answered Quickly
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
tomshaw83 , With gradient , you can use a measure that can give region
But you can create a color measure like and use that in conditional formatting using field value option
color = Switch( True() ,
max(Table[Region]) = "Region 1" && [% From World Average] <.30 , "Red",
max(Table[Region]) = "Region 1" && [% From World Average] <.50 , "Yellow",
max(Table[Region]) = "Region 2" && [% From World Average] <.50 , "Red",
max(Table[Region]) = "Region 2" && [% From World Average] <.50 , "Yellow",
// add other conditions
"Green"
)
How to do conditional formatting by measure and apply it on pie?: https://youtu.be/RqBb5eBf_I4
)
Hi,
This can be accomplished by using measures for conditional formatting:
Dax example:Conditional Formatting measure example =var _selected = max(YearlyOrders[Year])var _rank = RANKX(filter(all(YearlyOrders),YearlyOrders[Year]=_selected),[OrderValue],,DESC) returnswitch(true(),_rank=1,"Light Blue",_rank=2,"Blue")Format here:End result and explanation:So here I am ranking [ordervalue] based on selected year (in your example region and % measure). Then I use switch + true to give these ranks colors -> you can use text or hexacodes. These colors then work as a gradient.
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!Thank you, this is really helpful. I've implemented and tried to understand the code. I believe I have 2 problems that you may be able to help
1) you use max on the variable _selected, as dealing in years. As I have text is there something else I can use, at present it seems to return everything as Rank 1 - light blue
2) I've followed the implementation of the colour format, but these don't update the % world average column as in your example. If I add in the 'conditional formatting' as a new column it then updates the world average data colour (as in pic below). I'm not sure how to get this to be the background of the % values.
