Conditional Formatting Logic in Matrix based on Variance

[Caiz]

I'm trying to do conditional formatting with a traffic light column in my matrix.

This is what the traffic light is based on:

0-5% variance = GREEN

6-10% variance = ORANGE

11%+ variance =RED

I'd read on other forums because I was working with negative percentage variances so have been using:

95-100% -> -0.05 to 1

90-94% -> -0.16 to 0.1

89-0< -> -0.11

However, there is an error in one of my outputs -0.06 where it should be ORANGE instead of black. Everything else is correct.

How do I fix this?

[Anonymous]

Hi @Caiz ,

I made a number axis chart. As you can see in the figure, -0.06 is not within the condition range, so it is black.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Caiz]

Hi @Anonymous , I have changed the rule for Orange:

However, it still remains black.

[Anonymous · Accepted solution]

Hi @Caiz ,

If value is less than -0.06 means value <-0.06 not value <= -0.06. You can try to set if value is less than -0.055.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Caiz , Might need data /pbix to check that.

You can create a color measure like this.

Switch(True(),
[% variance] >-0.005 , "green",
[% variance] >-0.01 , "orange",
[% variance] >-1 , "red",
"blue"
)

and use that in coditional formatting, after using "Field Value" option

refer

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