Conditional formatting according to a value

[PwrBI01]

Hi guys,

I have a table with sales from 2020 and 2021 for different people and I would like to create a conditional formatting that do the following:

For the column Sales 2021 and variation I would like to do the following:

1- If the value for that person is higher than the average put a green color.

2- If the value for that person is lower than the average put a red color.

3- If the value for that person is equal to the average put a yellow color.

I would like to know how can I do 'Option 1' and 'Option 2'

The file is https://www.dropbox.com/s/v7r5mk7kvt9w041/Problem%20Power%20BI.pbix?dl=0

Thanks in advance.

Regards.

[V-lianl-msft · Accepted solution]

Please set these options separately:

[PwrBI01]

Thank you very much,

That is the solution I needed, so I will accept is as solution, but I have a new question that can be really easy but I don't know how to do it.

Variation 2021/2020 = (Sales 2021 - Sales 2020)/Sales 2020

So, if there is no data for Sales 2020 the result will be Blank, how can I put that if the result is blank it shouldn't show a color?, currently it will show a red color

[amitchandak]

@PwrBI01 , You can create a color measure.

assume you have measure [sales 2021]

color measure =

var _avg = calculate(averagex(values(Table[Name]), [sales 2021]), allselected())

return

switch(true() ,

[sales 2021] > _avg  , "green",

[sales 2021] = _avg  , "yellow",

[sales 2021] < _avg  , "red"

)

use it conditional formatting using the field value option

refer for steps

How to do conditional formatting by measure and apply it on pie?: https://youtu.be/RqBb5eBf_I4