Create RAG Status Logic for Increasing and Decreasing Metrics in Power BI
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.

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

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.




