Conditional formatting


Hello Members, I have this unique situation where I need to show both Actuals and Plan bars ( preferred). But if my Actuals are less than 25 % of the Plan ( which is true in this screenshot ) , I want to show Actuals bar as red color.. I am open to switch to different chart but at the end I want Actuals to be shown in red if it is less than 25% of Plan.


Thank You!



Tagged:

Best Answers

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤
    Accepted Answer

    @faisalnjit

    1) i would recommend a different visualization. consider the Bullet Chart which was designed to show target vs. actuals. by having data spread across two axis you force users to compare something in a way that may not be intuitive.


    By stacking Actuals and Plan into the same column of your data you've made it slightly more difficult to use the bullet chart and impossible to compare Actuals vs. Plan without using window function in your current chart type.

    SOLUTION. build beast modes for the plan and actuals

    sum( case when `version` = 'Plan' then `Amount` END
    
    sum( case when `version` = 'Actuals' then `Amount` END
    

    Now you have two separate metrics which you could use in a bullet chart provided that you DO NOT put version on the X Axis.

    By using this approach you could also build a beast mode that compares sum(...actuals..) vs. sum(...plan..)

    case
    when 
    sum( case when `version` = 'Plan' then `Amount` END ) >= sum( case when `version` = 'Actuals' then `Amount` END then 'Red' else 'Green'
    )
    

    Note, I generally don't recommend wrapping aggregate functions, SUM() in inside a CASE statement. It usually doesn't work as expected, so i would be unsurprised if it didn't work in the conditional formatting context.

  • faisalnjit
    faisalnjit ⚪️
    Accepted Answer

    Thanks all.

Answers