Forum Discussion

TM's avatar
TM
Icon for Advocate I rankAdvocate I
9 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

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

    Conditional Formatting Dialog

  • spuder's avatar
    spuder
    Icon for Resolver IV rankResolver IV
    9 years ago

    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))
  • v-caliao-msft's avatar
    v-caliao-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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#M77802

     

    Regards,

    Charlie Liao

  • KrisW's avatar
    KrisW
    Icon for Helper I rankHelper I
    8 years ago

    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)

     

  • sgsukumaran's avatar
    sgsukumaran
    Icon for Resolver II rankResolver II
    8 years ago

    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.