cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate I
Advocate I

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

Hello,

 

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,

TM

7 REPLIES 7
Highlighted
Resolver IV
Resolver IV

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

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))
Highlighted
Microsoft
Microsoft

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

@TM,

 

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.
https://community.powerbi.com/t5/Desktop/Conditional-formatting-based-on-dynamic-values/m-p/177823#M...

 

Regards,

Charlie Liao

Highlighted
Helper I
Helper I

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

FORMAT NEGATIVE NUMBERS RED, POSITIVE GREEN

 

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

Highlighted
Resolver II
Resolver II

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

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.

Highlighted
Frequent Visitor

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

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

Conditional Formatting DialogConditional Formatting Dialog

Highlighted
Frequent Visitor

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

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

https://youtu.be/smo-eqlWSt4?t=135

Highlighted
Frequent Visitor

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

@TM , @ocabrejo  posted the best anwer

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors