Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Need: Dynamic Column/Measure that conditionally formats VTF% based on VTF% values and Month

Hi, I have a table that includes a column for VTF%, and an traffic light icon to the right of that VTF% value. 

The traffic icon color depends on the VTF% and the month of the year.

Below are the parameters: 

Can you help me create a DAX formula for the color code that would dynamically apply the right color (green, yellow, or red) depending on the VTF% and the month of the year?
Thank you!

----------------------------------------------------------------------------

Color Code =

If Fiscal Month = Jul / Oct / Jan / Apr,

              Then if VTF% = -5% to 50%, conditional formatting icon = Green, else if VTF% > 50%, conditional formatting icon = Yellow, else if VTF% < -5%, conditional formatting icon = Red

If Fiscal Month = Aug / Nov / Feb / May,

              Then if VTF% = -5% to 25%, conditional formatting icon = Green, else if VTF% > 25%, conditional formatting icon = Yellow, else if VTF% < -5%, conditional formatting icon = Red

If Fiscal Month = Sep / Dec / Mar / Jun,

              Then if VTF% = -5% to 5%, conditional formatting icon = Green, else if VTF% > 5%, conditional formatting icon = Yellow, else if VTF% < -5%, conditional formatting icon = Red

 

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    Anonymous , Try a measure like

     

    Color Code =
    VAR _v_per = [VTF%] // Assumed it is a measure
    VAR _fsmonth = Max(Date[Fiscal Month]) //Assumed it is column and used in vsiaul

    RETURN
    SWITCH(
    TRUE(),
    _fsmonth IN {"Jul", "Oct", "Jan", "Apr" } && _v_per >= -.05 && _v_per <= .50, "Green",
    _fsmonth IN {"Jul", "Oct", "Jan", "Apr" } && _v_per > .50, "Yellow",
    _fsmonth IN {"Jul", "Oct", "Jan", "Apr" } && _v_per < -.05, "Red",

    _fsmonth IN {"Aug", "Nov", "Feb", "May" } && _v_per >= -.05 && _v_per <= .25, "Green",
    _fsmonth IN {"Aug", "Nov", "Feb", "May" } && _v_per > .25, "Yellow",
    _fsmonth IN {"Aug", "Nov", "Feb", "May" } && _v_per < -.05, "Red",

    _fsmonth IN {"Sep", "Dec", "Mar", "Jun" } && _v_per >= -.05 && _v_per <= .05, "Green",
    _fsmonth IN {"Sep", "Dec", "Mar", "Jun" } && _v_per > .05, "Yellow",
    _fsmonth IN {"Sep", "Dec", "Mar", "Jun" } && _v_per < -.05, "Red",

    "Other"
    )

     

     

    Also refer

    Field Parameters- Conditional Formatting
    : https://amitchandak.medium.com/field-parameters-conditional-formatting-517aacc23fdf
    Power BI Field Parameters, Keep Axis Sort intact| Always Sort on X/Categorial Axis: https://youtu.be/GfBrB6czByw

     

     

    How to do conditional formatting by measure and apply it on pie?
    https://www.youtube.com/watch?v=RqBb5eBf_I4&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L
    https://community.powerbi.com/t5/Community-Blog/Power-BI-Conditional-formatting-the-Pie-Visual/ba-p/1682539
    https://amitchandak.medium.com/power-bi-where-is-the-conditional-formatting-option-in-new-format-pane-66e0afcb15f3

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Amitchandak Can you please modify the code to accommodate the following scenarios:

      Current code you provided formats color when you select ONE fiscal month, whether it’s Jul, or Nov, or Mar, etc.

      But, I would like the code to accommodate the selection of more than 1 fiscal month.

      Assume M1 = 1st fiscal month of a fiscal quarter; M2 = 2nd’ M3 = 3rd, etc.

       

      Example:

      If user selects M1 + M2 (e.g. Oct + Nov), then the color format code should follow the rules as if only M2 were selected.

      If user selects M1 + M2 + M3 (e.g. Oct + Nov + Dec), then the color format code should follow the rules as if only M3 were selected.

       

      Can you modify the code for me? Thanks so much!

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi amitchandak Thank you !!! It worked LIKE A CHARM! You are a genius!!!