Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

My traffic light color shown wrong

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")

 

 

  • Hi Anonymous 

    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

9 Replies

  • negi007's avatar
    negi007
    Icon for Community Champion rankCommunity Champion

    Anonymous  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.

  • Hi Anonymous 

    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

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hi Phil

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

      Paul

  • Anonymous , 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

    • Anonymous's avatar
      Anonymous
      Not applicable

      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.

       

       

  • HI Anonymous 

    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