Showing results for 
Search instead for 
Did you mean: 
Advocate I
Advocate I

Conditional formatting - red on negative %, green on positive %



I am working on modifiying a report where we have a measure that finds out the change in sales of every product, displayed in percent. I need to format it accordingly: 


Positive change - green

Negative change - red

No change (0) - white


I've tried writing a calculated column with an IF statement that finds out if the change/index is positive, negative or zero and returns the value 1 if positive, -1 if negative and else 0. Unfortunately there's a similar calculated column we're using, and I get the circular dependency error message. 


We don't want any color gradient, but only a defined red, defined green and white. I can't figure this out. 


Any idea of how I can get around the circular dependency or any other suggestion on how to solve this? 


Kind Regards,


Frequent Visitor

I believe the right answer you are looking for was explained by this gentleman :

Not applicable

Center your formatting on 0 (Minimum).
If you have decimal places then your Max would be diferent.

Conditional Formatting DialogConditional Formatting Dialog

Not applicable

@TM , @ocabrejo  posted the best anwer

Resolver II
Resolver II

You can currently do conditonal formatting on individual measures green for +ve(0.0001) red for -ve(-0.0001) and white for 0. But this has to be done for every measure.

Helper I
Helper I



I went looking for the answer to this question this morning. Did not find the exact answer, but close enough to figure it out. FRUSTRATING that I needed to create another column (per field) I wanted to format this way. SAMPLE:


W2 Index = IF('Product Sales'[W2 Projected Inventory] < 0,-1,1)


Screen Shot 09-13-18 at 01.41 PM.PNG




Currently, conditional formating in not supported in Power BI since we can't add field value in conditional formatting. And it's not supported to set conditional formatting based on another column. A workaround is that add another column to assign static value and configure conditional formatting on that column. Please refer to the link below to see the details.



Charlie Liao

Resolver IV
Resolver IV

Hi @TM


could you give us some sample data for that.


I can't see why there should be a circular dependency when you just write a calculated column


ColorIndex = IF([VALUECOLUMN] < 0; -1; IF [VALUECOLUMN]=0;0;1))

Helpful resources

May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!