Menu
×
   ❮   
HTML CSS JAVASCRIPT SQL PYTHON JAVA PHP C C++ C# AWS W3.CSS HOW TO BOOTSTRAP REACT MYSQL JQUERY EXCEL XML DJANGO NUMPY PANDAS NODEJS DSA TYPESCRIPT ANGULAR ANGULARJS GIT POSTGRESQL MONGODB ASP AI R GO KOTLIN SWIFT SASS VUE GEN AI SCIPY CYBERSECURITY DATA SCIENCE INTRO TO PROGRAMMING HTML & CSS BASH RUST TOOLS MATH

Power BI Conditional Formatting


Share

What Is Conditional Formatting?

Conditional formatting changes the look of a value based on the value itself.

A low margin turns red.

A big sales number gets a darker cell.

It works much like conditional formatting in Excel.

In Power BI you use it mostly in tables and matrices, but chart colors can use it too.

There are five kinds of conditional formatting for tables and matrices:

KindWhat it does
Background colorColors the cell
Font colorColors the text
Data barsDraws a bar in the cell. The longer the bar, the bigger the value.
IconsAdds an icon, such as a colored circle or an arrow
Web URLTurns the value into a link

Colors can be set in three ways, called format styles: Gradient (a color scale), Rules (a color for each range of values), and Field value (the color comes from a measure).


The Example Table

Work in your file from the DAX chapter, for example poke_mart_dax.pbix (see Power BI Visuals for what it holds).

The table uses the measures Total Sales, Units Sold and Margin %.

Later on this page you add one more measure, Margin Color.

  1. Click an empty spot on the canvas and select Table.
  2. Add Product from the Products table, and then Total Sales, Units Sold and Margin %.
  3. Select the Total Sales column header until the table is sorted from the highest to the lowest value.

This is the table, before any formatting:

ProductTotal SalesUnits SoldMargin %
Poke Ball1,821,6009,10845.00%
Great Ball1,677,6002,79645.00%
Super Potion1,545,6002,20845.71%
Potion1,527,0005,09050.00%
Ultra Ball948,00079041.67%
Revive723,00048240.00%
Hyper Potion618,00051543.33%
Paralyze Heal195,60097852.50%
Awakening180,50072252.00%
Antidote179,9001,79955.00%
Total9,416,80024,48845.58%

You open conditional formatting from the field itself.

In the Visualizations pane, right-click a field in the Columns well (or select the arrow next to it), and select Conditional formatting.


Background Color Scale

A color scale colors each cell by its size: light for small values, dark for big values.

It works like color scales in Excel.

  1. Right-click Total Sales in the Columns well and select Conditional formatting > Background color.
  2. Set Format style to Gradient.
  3. Under What field should we base this on?, make sure Total Sales is selected.
  4. Set Minimum to Lowest value and pick white in the color box next to it. Set Maximum to Highest value and pick a green, for example #04AA6D: in the color box, select More colors... and type 04AA6D in the Hex box.
  5. Select OK.

The Background color conditional formatting dialog in Power BI Desktop for Total Sales, with Format style set to Gradient, based on Total Sales, Minimum set to Lowest value in white and Maximum set to Highest value in green

Power BI puts each value on a scale from the lowest (Antidote, 179,900) to the highest (Poke Ball, 1,821,600).

The position on the scale decides the shade:

ProductTotal SalesPosition on the scaleShade (approximate)
Poke Ball1,821,600100% Full green
Great Ball1,677,60091%
Super Potion1,545,60083%
Potion1,527,00082%
Ultra Ball948,00047% About halfway
Revive723,00033%
Hyper Potion618,00027%
Paralyze Heal195,6001% Almost white
Awakening180,5000% Almost white
Antidote179,9000% White

The position is (value - lowest) / (highest - lowest).

For Ultra Ball: (948,000 - 179,900) / (1,821,600 - 179,900) = 47%, so its cell is about halfway between white and green.

The top four products are all dark, and the three Status Healers are all white.

The scale makes the gap between the two groups easy to see.


Font Color Rules

A rule gives one color to every value in a range.

Here: show a margin below 45% in red.

  1. Right-click Margin % and select Conditional formatting > Font color.
  2. Set Format style to Rules.
  3. Fill in the rule: If value >= 0 Number and < 0.45 Number, then red (for example #D64550, with More colors... and the Hex box). The two unit boxes start as Percent: change both to Number.
  4. Select OK.

Three products turn red:

ProductMargin %Font color
Antidote55.00%Default
Paralyze Heal52.50%Default
Awakening52.00%Default
Potion50.00%Default
Super Potion45.71%Default
Poke Ball45.00%Default
Great Ball45.00%Default
Hyper Potion43.33%Red
Ultra Ball41.67%Red
Revive40.00%Red

Poke Ball and Great Ball have a margin of exactly 45.00%.

The rule says "less than 0.45", so they stay in the default color.

Use <= in the rule if 45% should also be red.

Note: For percentages, type the real decimal value (0.45, not 45) and choose Number, not Percent.

Percent means a percent of the range between the lowest and the highest value in the column, not the value itself.

This is what goes wrong with Percent.

The margins run from 40.00% to 55.00%.

A rule "< 45 Percent" then means "in the lowest 45% of that range", which is below 40% + 0.45 × 15% = 46.75%:

RuleBorderRed products
< 0.45 Number45.00%3
< 45 Percent46.75%6

With Percent, Super Potion, Poke Ball and Great Ball turn red too, although their margin is not below 45%.



Data Bars

Data bars draw a bar inside each cell, like data bars in Excel.

The longer the bar, the bigger the value.

  1. Right-click Units Sold and select Conditional formatting > Data bars.
  2. Leave Show bar only unchecked, so the numbers stay visible next to the bars.
  3. Select OK.

The bars rank the products by units, which is a different order than the sales order of the table:

Bar lengthProductUnits Sold
1 (longest)Poke Ball9,108
2Potion5,090
3Great Ball2,796
4Super Potion2,208
5Antidote1,799
6Paralyze Heal978
7Ultra Ball790
8Awakening722
9Hyper Potion515
10 (shortest)Revive482

Antidote is near the bottom of the table, but its bar is the fifth longest.

It sells many units at a low price.


Icons

Icons sort values into groups at a glance, like icon sets in Excel.

Here: a green, yellow or red circle for the margin.

  1. Right-click Margin % and select Conditional formatting > Icons.
  2. Set Format style to Rules.
  3. Keep Icon layout at Left of data, the default, so the icon shows to the left of the value.
  4. Under Style, choose the set of red, yellow and green circles. There is no set of plain circles: each circle has a symbol in it, a down chevron, a dash or an up chevron.
  5. Set the three rules as in the table below. The unit boxes start as Percent: choose Number in every rule.
  6. Select OK.
IconRule
Green circleIf value >= 0.5 Number and <= 1 Number
Yellow circleIf value >= 0.45 Number and < 0.5 Number
Red circleIf value >= 0 Number and < 0.45 Number

The products get these icons:

IconProducts
Green (50% or more)Antidote, Paralyze Heal, Awakening, Potion
Yellow (45% to under 50%)Super Potion, Poke Ball, Great Ball
Red (under 45%)Hyper Potion, Ultra Ball, Revive

Potion has a margin of exactly 50.00%, so it gets the green circle (>= 0.5).

The Margin % column now has both a font color and icons.

You can combine several kinds of conditional formatting in one column.


Color by Field Value

With Field value, the color comes from a measure that returns a color name or a hex code.

The rule lives in one measure, so every visual that uses it shows the same colors, and you change the rule in one place.

Create this measure in the Sales table:

Example

Margin Color = IF([Margin %] < 0.45, "#D64550", "#04AA6D")

The measure returns red for a margin below 45%, and green for the rest.

See DAX IF and SWITCH for more on IF.

Now use it to color the product names:

  1. Right-click Product in the Columns well and select Conditional formatting > Font color.
  2. Set Format style to Field value.
  3. Under What field should we base this on?, select Margin Color in the Sales table. It is the only field you can pick here. The other fields are grayed out, and while the mouse rests on a grayed-out field a tooltip covers the rows above it. Move the mouse off the list for a moment, then go straight to Margin Color.
  4. Select OK.
ProductMargin %Margin Color
Poke Ball45.00%#04AA6D (green)
Great Ball45.00%#04AA6D (green)
Super Potion45.71%#04AA6D (green)
Potion50.00%#04AA6D (green)
Ultra Ball41.67%#D64550 (red)
Revive40.00%#D64550 (red)
Hyper Potion43.33%#D64550 (red)
Paralyze Heal52.50%#04AA6D (green)
Awakening52.00%#04AA6D (green)
Antidote55.00%#04AA6D (green)

Notice that Product is a text column.

You can still format it, because the color comes from a measure.

The finished Poke Mart table in Power BI with a white-to-green background scale on Total Sales, data bars on Units Sold, red font and colored circle icons on Margin %, and product names colored green or red by the Margin Color measure


Conditional Colors in a Column Chart

Charts can use the same measure.

Here, a column chart of sales by product, with red columns for the low-margin products:

  1. Click an empty spot on the canvas and select Clustered column chart. Add Product to the X-axis well and Total Sales to the Y-axis well. The columns are sorted from the highest to the lowest Total Sales.
  2. Select Format visual, and on the Visual tab expand Columns.
  3. Next to Color, select the fx button.
  4. Set Format style to Field value, and under What field should we base this on? select Margin Color.
  5. Select OK.
ColumnTotal SalesColor
Poke Ball1,821,600Green
Great Ball1,677,600Green
Super Potion1,545,600Green
Potion1,527,000Green
Ultra Ball948,000Red
Revive723,000Red
Hyper Potion618,000Red
Paralyze Heal195,600Green
Awakening180,500Green
Antidote179,900Green

A Power BI clustered column chart of Total Sales by Product where Ultra Ball, Revive and Hyper Potion are red and the other seven products are green, with the Columns section of the Format visual pane showing the fx button next to Color

The three red columns sit together in the middle of the chart: mid-sized sales, but the lowest margins.

In the Columns section, the fx button opens the conditional formatting again, and the eraser button next to it removes it.


Remove Conditional Formatting

In a table or matrix, right-click the field in the Columns well, select Remove conditional formatting, and choose which kind to remove.

Note: Reset to default in the Format visual pane also removes conditional formatting rules.

Use it with care on a visual you have spent time on.

Good job! You have finished the Visuals chapter.

You can now build cards, KPIs, charts, slicers and formatted tables from your own Power BI model.


What's Next

Test what you learned with the Power BI Exercises.

This is the last chapter of the Power BI tutorial for now.

New chapters are on the way: Reports, where you turn your visuals into a finished report with several pages, and Sharing, where you publish the report to the Power BI service and share it with others.



Free account

Track your progress

XP earned 0 Day streak 0 Spaces 0 League —

New

Learn in Adventure Mode W3Schools Adventure App

Coding fundamentals as bite-sized lessons and challenges.

×

Contact Sales

If you want to use W3Schools services as an educational institution, team or enterprise, send us an e-mail:
sales@w3schools.com

Report Error

If you want to report an error, or if you want to make a suggestion, send us an e-mail:
help@w3schools.com

W3Schools is optimized for learning and training. Examples might be simplified to improve reading and learning. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. While using W3Schools, you agree to have read and accepted our terms of use, cookies and privacy policy.

Copyright 1999-2026 by Refsnes Data. All Rights Reserved. W3Schools is Powered by W3.CSS.