Others

Create RAG Status Logic for Increasing and Decreasing Metrics in Power BI

John WilsonJohn Wilson Nov 13, 2026 869 views

Question details

The user needs to create Power BI RAG status logic that evaluates both increasing metrics, where higher values are better, and decreasing metrics, where lower values are better.

Create RAG Status Logic for Increasing and Decreasing Metrics in Power BI
Product
Microsoft Power BI
Device & OS
not provided
Scenario
Building KPI status indicators for a metrics table that contains mixed direction metrics and targets.
Observed behavior
A single formula that assumes higher is better will classify decreasing metrics such as costs or response times incorrectly.
Before you start

Before creating the measure, confirm that your metrics table has columns for Direction, Target, AmberThreshold, and Value, or that these values can be supplied through slicers or selected values.

Solution 1Recommended

Use a Direction Column and SWITCH Measure for RAG Status in Power BI

Use this approach when you need one RAG status measure that correctly handles both increasing metrics, where higher is better, and decreasing metrics, where lower is better.

Because metrics such as revenue and response time require opposite logic, a single formula that assumes higher is better will misclassify costs or duration metrics. Adding a Direction column lets the measure branch correctly for both increasing and decreasing metrics.

1
Add a Direction column

In Power BI Desktop, open Power Query Editor, select your Metrics table, and add a column named Direction containing the exact values Increasing or Decreasing for each metric.

2
Create the RAG Status measure

Go to the Modeling tab and click New measure. Enter a measure named RAG Status and define variables for Direction, Target, AmberThreshold, and Value based on SELECTEDVALUE from your Metrics table.

3
Enter the direction-aware SWITCH expression

In the formula bar, enter the following conditional logic: SWITCH(TRUE(), Direction = "Increasing" && Actual >= Target, "Green", Direction = "Increasing" && Actual >= Target - Amber, "Amber", Direction = "Decreasing" && Actual <= Target, "Green", Direction = "Decreasing" && Actual <= Target + Amber, "Amber", "Red").

4
Apply the measure in a visual

Add the RAG Status measure to a table, matrix, or KPI visual, then use conditional formatting to map Green, Amber, and Red to background or font colors.

Use a Direction Column and SWITCH Measure for RAG Status in Power BI
Percentage thresholds: If your amber threshold is a percentage rather than an absolute value, adjust the amber calculation accordingly, for example Actual >= Target * (1 - AmberPercent) for increasing metrics.
Free Microsoft Office alternative

Use WPS Office as a Free Spreadsheet and Reporting Companion for Power BI Workflows

RAG status logic must be built inside Microsoft Power BI with DAX, and WPS Office does not edit Power BI reports. However, WPS Office is a free, lightweight, Office-compatible suite for preparing metric tables, documenting RAG definitions, and sharing reports alongside Power BI.

Free Office suite for preparing and documenting Power BI RAG status metric tables.Opens and saves Excel .xlsx workbooks with full Microsoft Office format compatibility.Familiar ribbon interface reduces the learning curve when switching from Microsoft Office.Built-in PDF tools make it easy to share status dashboards and reports.
microsoft office alternative - wps office

Frequently Asked Questions

How do I make the RAG status work when a lower value is better?

Use a Direction column that contains Decreasing for those metrics. In the SWITCH expression, use the branch Direction = "Decreasing" && Actual <= Target to return Green, and Direction = "Decreasing" && Actual <= Target + Amber to return Amber.

Can I use percentage amber thresholds instead of absolute values?

Yes. Adjust the amber comparison to use a percentage. For increasing metrics, use Actual >= Target * (1 - AmberPercent), and for decreasing metrics, use Actual <= Target * (1 + AmberPercent).

Why is my RAG measure returning Red for everything?

Check that the Direction values exactly match Increasing and Decreasing, that Target, AmberThreshold, and Value are all available through SELECTEDVALUE or row context, and that the measure is being evaluated with the correct relationships and filters.

Can I apply RAG colors directly in a Power BI visual?

Yes. Add the RAG Status measure to a table or matrix visual and open conditional formatting. Then set the formatting to use field values or rules so Green, Amber, and Red map to the desired colors.