Conditional Formatting
Conditional formatting in Power BI changes the appearance of data based on values, thresholds, or rules. It helps users identify high-performing categories, low results, exceptions, and important business trends without examining every number individually.
Learning Objectives
- Understand conditional formatting and its business purpose.
- Apply background colors and font colors based on values.
- Use data bars, icons, and color scales where supported.
- Understand rule-based and field-based formatting.
- Apply conditional formatting to tables and matrices.
- Practice formatting business performance data.
1. What Is Conditional Formatting?
Conditional formatting applies visual changes when data meets specified conditions. Instead of showing every value in the same style, Power BI can emphasize values that meet a rule or fall within a particular range.
Example: Sales Performance by Region
In this example, the colors help the user find regions that need attention. The labels also explain the meaning of each status, so the report does not depend on color alone.
2. Where Can Conditional Formatting Be Used?
| Formatting Type | Purpose | Example |
|---|---|---|
| Background color | Highlights the background of values. | Highlight low sales in red. |
| Font color | Changes text color based on a condition. | Show negative profit in red text. |
| Data bars | Shows relative magnitude using bars. | Compare revenue across products. |
| Icons | Uses symbols to represent status or direction. | Show upward or downward performance indicators. |
| Color scales | Maps values to a range of colors. | Highlight high and low values in a matrix. |
3. Apply Background Color Formatting
Background color formatting is useful when users need to identify values that meet specific rules. For example, a sales table can highlight results below a target.
Steps in Power BI Desktop
- Select a table or matrix visual on the report canvas.
- Find the field or measure in the visual's data configuration.
- Open the field menu or formatting options for that value.
- Choose Conditional formatting, then select Background color where available.
- Choose a formatting method, such as rules or a color scale.
- Set the field, thresholds, and colors.
- Apply the settings and verify the result.
Example Rules
| Sales Value | Formatting Rule | Example Color |
|---|---|---|
| Less than $50,000 | Below target | Light red |
| $50,000 to $80,000 | Needs review | Light amber |
| Greater than $80,000 | Strong result | Light green |
4. Apply Font Color Formatting
Font color formatting changes the color of text when values meet specified conditions. It can be useful for profit, variance, growth, and other metrics where positive and negative values have different meanings.
Example: Profit Variance
A common approach is to display favorable variance in green and unfavorable variance in red. Always consider the metric: for costs, a negative variance may represent savings rather than a poor result.
5. Use Data Bars
Data bars display horizontal bars alongside numerical values. Longer bars represent larger values, making it easier to compare rows in a table or matrix.
Revenue by Product
Steps to Add Data Bars
- Select a table or matrix visual.
- Open the conditional formatting options for the numerical field.
- Choose Data bars, where supported.
- Configure the bar color and relevant display options.
- Review the bars and numerical values together.
6. Use Icons for Status Indicators
Icons can provide quick visual signals for performance. For example, arrows can indicate direction, and status symbols can show whether values meet a target.
Performance Indicator Examples
Icon rules should reflect the metric being measured. A downward arrow may be unfavorable for revenue but favorable for expenses. Include labels or values so that users can interpret the status correctly.
7. Apply Color Scales to a Matrix
Color scales use a range of colors to represent numerical values. They are useful when users need to scan a matrix and quickly identify higher and lower values across several categories.
Monthly Sales by Region
Steps to Apply a Color Scale
- Select a matrix or table visual.
- Open conditional formatting for the relevant numerical field.
- Choose background color or another supported formatting option.
- Select a color scale and configure its minimum, midpoint, and maximum where available.
- Check the contrast between cell backgrounds and text.
8. Use a Measure for Conditional Formatting
A DAX measure can return a color value based on a business condition. When the selected visual supports field-value formatting, the measure can be used to control the color.
Example DAX Measure
Sales Status Color =
VAR SalesValue = [Total Sales]
RETURN
SWITCH(
TRUE(),
ISBLANK(SalesValue), "#9CA3AF",
SalesValue < 50000, "#FEE2E2",
SalesValue <= 80000, "#FEF3C7",
"#DCFCE7"
)
This measure returns a hexadecimal color based on the value of
[Total Sales]. It assumes that the
[Total Sales] measure already exists in the model.
How to Use the Measure
- Create the measure in Power BI Desktop.
- Select a table or matrix visual that supports field-value formatting.
- Open the conditional formatting options for the appropriate field.
- Choose the field-value formatting method.
- Select
Sales Status Coloras the field providing the color. - Apply the settings and test several values.
9. Practical Exercise
Scenario: Monthly Sales Analysis
A company wants to identify products that are performing well and products that need attention. The report contains product name, region, total sales, profit, and sales target.
Tasks
- Create a table showing product name, sales, profit, and target.
- Apply background color formatting to sales values.
- Use font colors to distinguish positive and negative profit variance.
- Add data bars to compare sales across products.
- Use icons to indicate performance direction where appropriate.
- Apply a color scale to a matrix of monthly sales by region.
- Create a DAX color measure and use field-value formatting.
- Check that the table remains readable and includes clear labels.
Expected Outcome
Users should be able to identify high and low sales, spot negative profit results, compare categories, and recognize performance exceptions without reading every value individually.
10. Knowledge Check
Question 1: What is conditional formatting?
A. Formatting visuals based on data values or conditions
B. Removing all filters from a report
C. Creating a new data source
Question 2: Which formatting type displays bars beside numerical values?
A. Data bars
B. Page navigation
C. Report themes only
Question 3: What is a color scale useful for?
A. Comparing relative values across a table or matrix
B. Renaming report pages
C. Changing a data model relationship
Question 4: What can a DAX color measure return?
A. A color value such as a hexadecimal color code
B. A new report page automatically
C. A database password
Question 5: Why should status labels accompany colors where appropriate?
A. To make the report harder to read
B. To help users understand meaning without relying on color alone
C. To remove the need for data
Show Answers
- A — Conditional formatting changes appearance based on conditions.
- A — Data bars provide a visual comparison of magnitudes.
- A — Color scales help users scan relative values.
- A — A color measure can return a valid color value.
- B — Labels provide additional context and improve accessibility.
Summary
Conditional formatting makes important business information easier to identify by changing visual appearance according to values or rules. Power BI supports several formatting approaches for supported visuals, including color scales, data bars, icons, and field-value formatting.
- Use conditional formatting to highlight meaningful patterns and exceptions.
- Apply background and font colors using suitable rules.
- Use data bars to compare relative magnitudes.
- Use icons to communicate performance status or direction.
- Apply color scales to tables and matrices when appropriate.
- Use DAX measures for dynamic colors when supported.
- Keep thresholds meaningful, labels clear, and text readable.