Conditional Formatting Rules

[eladumitrascu]

I've created a series of rules to enable a RAG traffic system to track performance against targets. The tolerance is set at 10%, with the folllowing conditions:

• Green: Actual meets or exceeds the target.
• Amber: Actual is below the target but within 10% tolerance.
• Red: Actual is more than 10% below the target.

Currently, I calculate this manually. In the example below, the target is 83 and I worked out the 10% tolerance and adjusted the thresholds accordingly. However, the target changes each month so having the current set up isn't efficient, because I have to recalculate and update the thresholds every time the target changes. 

What I’d like to do is define the RAG rules dynamically using percentage differences, rather than fixed values. So instead of rules like:

If value >= 0 and < 74.7, then red

I want something more like:

If the actual is more than 10% below the target, then red.

[Akash_Varuna · Accepted solution]

Hi @eladumitrascu Try this please 
Create a calculated Column or Measure
Formula : 

RAGStatus = 
IF(
    [Actual] >= [Target], 
    "Green", 
    IF(
        [Actual] >= [Target] * 0.9, 
        "Amber", 
        "Red"
    )
)

• In the visual, add the RAGStatus column or measure.
• Apply Conditional Formatting:
  • Go to the "Values" area of the visual.
  • Select Conditional Formatting → Background Color or Font Color.
  • Use rules based on the RAGStatus column:
    • Green → Hex (#00FF00)
    • Amber → Hex (#FFC000)
    • Red → Hex (#FF0000)
      If this post helped please do give a kudos and accept this as a solution
      Thanks In Advance

[eladumitrascu]

Hi, thanks for this. Would this calculated measure work for different metrics, or would I need to create an individual measure for each specific metric that I want to compare against targets? Thanks

[shafiz_p]

Hi @eladumitrascu  Try this:

RAG Status = 
VAR Target = [Target]
VAR Actual = [Actual]
VAR Tolerance = 0.1 * Target
RETURN
    SWITCH(
        TRUE(),
        Actual >= Target, "Green",
        Actual >= Target - Tolerance, "Amber",
        "Red"
    )

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz