Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
deanbland
Helper III
Helper III

Help with conditional formatting!

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

deanbland_0-1612456698388.png

 

Thanks in advance! 

 

 

 

1 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")





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

5 REPLIES 5
v-kelly-msft
Community Support
Community Support

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
Super User
Super User

@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:

VijayP_0-1612457584999.png

Please let me know if any questions




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


Hi, 

 

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

 

deanbland_0-1612522281903.png

 

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!

 

@deanbland 

Try:

SWITCH(TRUE(),

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

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






amitchandak
Super User
Super User

@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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.