Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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!
Solved! Go to Solution.
Try:
SWITCH(TRUE(),
[Your measure] < 0.81, "red",
[Your measure] > 1.19, "red,
[Your measure] >= 0.95 && [Your measure] <= 1.05, "green",
"orange")
Proud to be a Super User!
Paul on Linkedin.
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!
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
Proud to be a Super User!
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!
Try:
SWITCH(TRUE(),
[Your measure] < 0.81, "red",
[Your measure] > 1.19, "red,
[Your measure] >= 0.95 && [Your measure] <= 1.05, "green",
"orange")
Proud to be a Super User!
Paul on Linkedin.
@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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
68 | |
53 | |
39 | |
33 |
User | Count |
---|---|
70 | |
63 | |
57 | |
49 | |
46 |