Help with conditional formatting!

[deanbland]

HI, 

I have a matrix table (shown below) and I am wanting to conditional format the cells within each column in the following way: 

- Between -5 and +5, they are highlighted Green

- Above +5 and below -5, but less than +19 and more than -19 highlighted Amber

- Above +19 and below -19 - Red 

I am having difficulty doing this within the conditional formatting window of PBI so wondered if there was a DAX formula I could use? 

Table - the conditional formatting is wrong (obviously). 

Thanks in advance! 

[v-kelly-msft]

Hi @deanbland ,

Are you using 1 measure or multiple measures?Can you share your .pbix file if there's no confidential information inside?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[VijayP]

@deanbland 

You need to create a Measure

SWITCH(TRUE(),
YourMeasure>= -5 && YourMeasure <=5, "Green",
YourMeasure<=-19&&YourMeasure>=19 ,"#DC8210", "red")

Then use this In conditional Formatting using Field Value

as shown below:

Please let me know if any questions

[deanbland]

Hi, 

Thank you for responding. When using the measure suggested above, it doesn't work as expected. 

It is turning every number green. I have tried changing the measure with no luck. It seems to constantly highlight all numbers green, regardless of their value. 

I am wanting everything between -5% and +5% green and everything below -19% and above 19% red. 

Thanks again!

[PaulDBrown · Accepted solution]

@deanbland 

Try:

SWITCH(TRUE(),

[Your measure] < 0.81, "red",
[Your measure] > 1.19, "red,

[Your measure] >= 0.95 && [Your measure] <= 1.05, "green",
"orange")

[amitchandak]

@deanbland , you can create a color measure and use that in conditional formatting with Field value option

Color sales = if(AVERAGE(Sales[Sales Amount])<170,"green","red")
Color Year = if(FIRSTNONBLANK('Table'[Year],2014) <=2016,"lightgreen",if(FIRSTNONBLANK('Table'[Year],2014)>2018,"red","yellow"))

refer for steps

https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-num...
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values