Power BI Conditional Formatting Based on a Measure (Colors, Icons and Charts)

Red, amber and green cells only help when the colors follow your business rule, like “below 90% of target is red.” A plain gradient can’t know that rule, but a DAX measure can.

In this tutorial, we’ll set up power bi conditional formatting based on measure values on a real 2026 sales vs target table: a color measure for the cell background, icons driven by a second measure, and a column chart colored by the same logic. We’ll also fix the reasons it often refuses to work.

Quick answer: Create a measure that returns a color as text, such as SWITCH(TRUE(), [% of Target] >= 1, “#1E8E3E”, …). Then right-click the field in the visual, select Conditional formatting > Background color, set Format style to Field value and pick that color measure. Numeric measures can’t be used with Field value.

What You Need Before You Start

• Power BI Desktop (the same dialogs exist when you edit a report in the Power BI service).
• A table, matrix or chart with at least one grouping field, such as Region.
• A numeric measure that holds your rule, plus a text measure that turns it into a color.

My demo files are two CSVs loaded with Get data > Text/CSV: Sales (60 orders, $165,701) and Targets (one target per region). New to loading data? Start with creating your first Power BI report. Here’s the table we’ll format:

Region	Total Sales	Total Target	% of Target
Central	$3,419	$5,000	68.38%
East	$81,686	$75,000	108.91%
South	$16,813	$15,000	112.09%
West	$63,783	$70,000	91.12%
Total	$165,701	$165,000	100.42%

Total Sales is SUM(Sales[Sales]) and Total Target is SUM(Targets[Target]). The percentage measure comes from our guide to calculating the percentage of two columns:

% of Target = DIVIDE([Total Sales], [Total Target])

Step 1: Create a Color Measure With SWITCH

Select Home > New measure and enter the measure below. SWITCH(TRUE(), …) checks each condition in order and returns the first match, so the highest threshold goes first.

Target Color =
SWITCH(
    TRUE(),
    [% of Target] >= 1, "#1E8E3E",
    [% of Target] >= 0.9, "#F2A900",
    "#D93025"
)

Notice the Format box says Text. Every result is a hex code with the # sign, and all results must share one data type. Our Power BI SWITCH function guide has more patterns, and IF with multiple conditions in a measure shows the nested IF version.

On our data, Central returns red, West amber, and East and South green.

Step 2: Open Conditional Formatting on the Field

Select the table visual. In the Columns well, right-click % of Target (or use its down arrow), then select Conditional formatting > Background color.

The same submenu has Font color, Data bars, Icons and Web URL. Font color uses the exact same dialog, so this recipe works for text color too.

Step 3: Set Field Value and Pick the Color Measure

The Background color – % of Target dialog opens with Format style set to Gradient. Change it to Field value. Power BI keeps % of Target in the base field box, outlines it in red and disables OK:

Open the field list and you’ll see why. Every numeric measure and column is grayed out, while text fields like Category and City stay selectable:

Hover over a grayed field and Power BI explains it:

Field value expects a color, so it only offers text fields. Pick Target Color instead, and OK turns active. Leave Apply to on Values only for now.

Step 4: Check the Result

Select OK. Each % of Target cell now takes its color from the measure:

Central (68.38%) is red, West (91.12%) is amber, and East and South are green. The Total row stays plain because Apply to is Values only. Change Apply to if you want totals colored as well.

Step 5: Add Icons Based on Another Measure

Here’s the “format one field by a second measure” case. Right-click Total Sales > Conditional formatting > Icons. Power BI starts with Rules on Total Sales itself, using Percent bands:

Percent here doesn’t mean 33% of target. It’s a position between the lowest and highest value in the column, so it won’t match our rule.

Change What field should we base this on? to % of Target, set every type to Number and type decimals:

If value >= 0   Number and < 0.9  Number  then red diamond
If value >= 0.9 Number and < 1    Number  then amber triangle
If value >= 1   Number and <= 100 Number  then green circle

Central gets a red diamond, West an amber triangle, and East and South green circles, matching the backgrounds. For the other table options, see our Power BI table conditional formatting guide.

Step 6: Color Chart Columns by the Measure

Charts use the same measure. We built a column chart with Targets[Region] on the X-axis and Total Sales on the Y-axis. In Format visual > Columns > Color, select the fx button.

The Color – Categories dialog starts with Rules based on Region. Switch to Field value and pick Target Color:

Central is red, East and South are green and West is amber. Numeric fields are grayed out here too. For bars with several values, see bar chart conditional formatting with multiple values or our clustered column chart tutorial.

Cheat Sheet: Which Format Style to Use

Format style	Based on	Best for
Gradient	Numeric field or measure	Heat-map shading from low to high
Rules	Numeric field or measure	A few fixed bands (use Number for percentages)
Field value	Text measure returning a color	Business logic, text-based colors, charts
Icons (Rules)	Any numeric measure	Status icons next to another value

Common Mistakes: Why Conditional Formatting Based on a Measure Isn’t Working

• A numeric measure in Field value. It’s grayed out with “a matching data type is required.” Pick a text measure that returns colors, or use Rules.
• Percent instead of Number. Percent bands follow the min-max range. For a percentage measure, type 0.9 and pick Number.
• Hex codes without the #. Power BI reads #1E8E3E, RGB(…), HSL(…) and names like Green.
• Mixed result types. If one SWITCH branch returns a number and another text, the measure errors.
• Divide by zero. NaN values break automatic gradients and Percent rules. Build ratios with DIVIDE().
• Wrong condition order. SWITCH returns the first match, so put >= 1 before >= 0.9.

Can You Use Rules Instead of a Color Measure?

Yes. Rules on % of Target with Number thresholds give the same colors without a new measure. We prefer the color measure when several visuals share one rule, because you change it once. To color by a text column, see conditional formatting based on field value and formatting based on text.

Next Steps

Try a Font color rule with the same Target Color measure, or add a data bar to Total Sales. For more report tutorials, browse our Power BI tutorials.

We’d try these next with the same sales model:

• How to Apply Conditional Formatting in Power BI Pie Chart?
• Power BI Conditional Formatting Positive and Negative Numbers
• Difference Between Calculated Column and Measure in Power BI
• Power BI Percentage of Total by Category [With Real Examples]

Frequently Asked Questions