My traffic light color shown wrong

[Paulyeo11]

Hi All

Color_GP =

VAR _variance_per_country =

[GP% YTD]

RETURN

SWITCH (

TRUE (),

_variance_per_country < 0.15 && _variance_per_country >= -100, "#D64550",

_variance_per_country < 0.4 && _variance_per_country >= 0.15, "#E8D166",

_variance_per_country <= 10 && _variance_per_country >=0.4, "#79FF00",

"#00000")

[PhilipTreacy]

HI @Paulyeo11 

I'm confused, maybe others are too.

Your initial post showed a measure being created based on the value of [GP% YTD] and the image showed [GP% VARI] being conditionally formatted based on the result of that new measure.

Is this what you want?  To format [GP% VARI]  based on the value of [GP% YTD] ?

Or do you want [GP% VARI] formatted based on it's own value?

Either way you don't need to create a new measure, you can set your conditional formatting rules as I've shown.

FWIW, the final value in the SWTCH statement is missing a 0, the hex colour value should be "#000000"

Regards

Phil

[amitchandak]

@Paulyeo11 , it should be

Color_Sales = 
VAR _variance_per_country =
[Sales Vari %]
RETURN
SWITCH (
TRUE (),
_variance_per_country < 0  , "red",
_variance_per_country < 0.2 && _variance_per_country >= 0, "green",
_variance_per_country <= 10 && _variance_per_country >=0.2, "yellow",
"#00000"
)

or

Color_Sales = 
VAR _variance_per_country =
[Sales Vari %]
RETURN
SWITCH (
TRUE (),
_variance_per_country < 0  , "red",
_variance_per_country < 0.2 && _variance_per_country >= 0, "yellow",
_variance_per_country <= 10 && _variance_per_country >=0.2, "green",
"#00000"
)

Find the file attached

[Paulyeo11]

Hi Amit

i just check both of your expression not work , for example if you notice that below table i need all -ve value show in red color. now some show red color only.

[amitchandak]

@Paulyeo11 , Your color formula is on sales Var % not on GP Var %. You can change the column in Var as per need

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[PhilipTreacy · Accepted solution]

Hi @Paulyeo11 

Download the PBIX file with working CF rules

You don't need to create a separate measure for this, you can format the conditional formatting rules directly for GP% Variance 

Same for the font

Regards

Phil

[Paulyeo11]

Hi Phil

Thank you very much for give me detail step , first time i ever try using this apporach , it work

Paul

[negi007]

@Paulyeo11  I will suggest you another way of doing it, you first create a measure that will be used for color coding like below.  You can change or modify the condition as per your requirment.

Color_Code =

if (and([GP% YTD] < .15,[GP% YTD] >= -100), 1,
if(and([GP% YTD] < .4,[GP% YTD] >= 0.15), 2,
if(and([GP% YTD] < 10,[GP% YTD] >= 0.4), 3 ,
4)
)
)

Once you create above measure, you can then use above result in the conditional formatting. Conditional formatting in the matrix will change as per the result of the measure created above.

[Paulyeo11]

Hi Neg

Thank you for your sharing. 

I try to follow your approach , but then i am not able to select  the field and move the condition format.

https://www.dropbox.com/s/zv46wuy115fo8a8/PB_TDS_V100%20ask%20GP%20TRAFFICE%20LIGHT.pbix?dl=0

My PBI fle at able link

Paul