Forum Discussion

tomshaw83's avatar
tomshaw83
Helper I
4 years ago
Solved

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 forma...
conditonal formatting
needhelp
  • v-robertq-msft's avatar
    v-robertq-msft
    4 years ago

    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.

ValtteriN's avatar
ValtteriN
Community Champion
4 years ago

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) return

switch(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!


 

tomshaw83's avatar
tomshaw83
Helper I
4 years ago

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.