Conditional Formatting in Power BI

In Power BI, you can use conditional formatting in various ways, such as formatting individual items in your visuals or changing the data label’s font or title colour of a chart. It helps in highlighting important values or patterns in your data. Power BI provides an easy way to apply basic conditional formatting to change the background and font colour of cells in a table or matrix. In this blog, you will learn how to apply conditional formatting to your data in detail.

Table of Contents:

• What is Conditional Formatting in Power BI?
• Why is Conditional Formatting Important in Power BI?
• Advantages of Using Conditional Formatting in Power BI
• Where can you apply Conditional Formatting?
  1. Tables and Matrices
  2. Bar charts
• Steps to Apply Conditional Formatting in Power BI
• Conditional Formatting Issues and Solutions in Power BI
• Real-World Example
• Performance Consideration
• Best Practices
• Conclusion

What is Conditional Formatting in Power BI?

Conditional Formatting in Power BI is a way of representing your data based on the rules defined by the user. You can use colour, icons, or data bars to highlight important information. Conditional Formatting turns a normal table into an interactive visual by applying formatting rules. It helps highlight key trends, outliers, or important values using colour, making complex data clearer. There is no need for the user to manually scan for key points or important points in data, but with the help of conditional formatting, the user can spot important data instantly.

Why is Conditional Formatting Important in Power BI?

Conditional formatting is essential because it enables users to quickly interpret data patterns that might otherwise be hidden. Conditional formatting can identify trends, outliers, or markers of key metrics in data by colour and icon; this makes the information meaningful. Conditional formatting helps to consider readings more efficiently as it highlights key information for decision-making.

Advantages of Using Conditional Formatting in Power BI

• It helps improve visualisation by making important information pop out.
• It promotes decision-making by highlighting exceptions and trends.
• It improves the explanation of reports by making information available to the user in a simpler way.
• It enables users to identify trends more easily.
• It helps in improving data presentation.
• It helps in improving the user experience.

Be the Power BI expert companies need!

Enroll now and become a Power BI expert today!

Explore Program

Where can you apply Conditional Formatting?

Let’s create a dataset that we will use to apply conditional formatting to various visuals.

Load this dataset into your Power BI

1. Tables and Matrices

These visuals allow formatting of the cell, row, and column levels to highlight performance against KPIs.

Step 1: Insert a Table from the visual pane and select  Region, Sales, and Target

Step 2: Click the dropdown next to ‘Sum of Sales’ to enable Conditional Formatting.

Here, this image shows a drop-down icon that needs to be clicked to enable Conditional Formatting.

After clicking on the Dropdown, a bar will appear with multiple options. You need to select Conditional Formatting >  Background colour.

Click on Conditional Formatting and choose background colour.

Step 3: Set Format by Rules

Add Rules:

• If the value is less than 1000 = Red colour
• If the value is greater than or equal 1000=Green colour

Output:

Explanation: The table highlights East and North in red as their sales are below 1000, while South and West are shown in green for exceeding the target.

2. Bar charts

Step 1: Insert a Clustered Bar Chart

Drag the Region in Axis and Sales in Values

Step 2: Create a new DAX measure:

SalesColor =
IF(SUM('Table'[Sales]) >= SUM('Table'[Target]), "#4CAF50", "#F44336")

Here, #4CAF50 and #F44336 are hex codes for green and red, commonly used in web design and fully supported by Power BI for conditional colour formatting.

Note: Ensure the table name (‘Table’) is appropriately replaced with the actual table name used in your dataset

After writing this DAX function, a new feature is created.

Note: For the DAX measure to work correctly with colour formatting, ensure it’s returning a text hex code, which is a hexadecimal colour code, and is used under Field value in Data Colour.

Step 3: Click on bars in the visual > Data colours in the Format pane.

Step 4: Click the fx (Function button) next to the Default colour

Step 5: Set

• Format by Field values
• Based on the field SalesColor

Result:

Explanation: Here, this chart shows Red colour for East and North as they have sales below 1000, while South and West have green colour as sales are above 1000.

Steps to Apply Conditional Formatting in Power BI

Let’s discuss how to apply conditional formatting with the help of a sample dataset.

Example: Let’s consider a Sales table containing monthly revenue, target achievement, and region-wise performance:

Step 1: Load the dataset into Power BI

Open Power BI> Click on Get Data> Text/CSV

After following the mentioned steps, this is how the table looks after loading it in Power BI.

Step 2: Select the Table Visual from the Visualisation Panel

Step 3: Result

This is the final table created, but it is difficult to read as conditional formatting is not applied.

Step 4: Applying Conditional Formatting

• Go to the Format pane > expand Conditional Formatting
• Choose the measure or field in which you want to apply formatting. We have chosen the % field for applying formatting

Step 5: Click Conditional Formatting > Background colour

Step 6: Apply Rules

Choose Format by Rules > select a condition based on the % field

• If the value is >1.00, choose the Green colour.
• If the value is >=0.90 <1.00, choose the Yellow colour.
• If the value is <0.90, choose the Red colour.

These rules will help to add colour to the table, which helps the user to identify the strong and weak parts of the table.

Result:

Explanation: Here, the green colour shows values that are above or equal to the target value, and the red colour shows values that are less than the target value.

Conditional Formatting Issues and Solutions in Power BI

Issue 1: Formatting Not Showing

Conditional Formatting will not apply if the correct values are not placed in the appropriate section. Make sure the column you are formatting is properly added under the correct field.

Issue 2: Wrong Colour Logic

When formatting the columns with the help of rules, make sure to give correct rules and logic flows from top to bottom.

Issue 3: Colour Not Updating

Make sure you refresh the visual or refresh the data model to reflect the latest formatting changes by the user.

Issue 4: Formatting Applied on the Wrong Field

Make sure you are selecting the right column for formatting. Selecting the wrong column will lead to wrong results.

Issue 5: Percentage-Based Rules Not Working as Expected

Ensure the % column is treated as a decimal (e.g., 0.90 for 90%) and not a formatted percentage string.

Real-World Example

A regional manager of a retail chain uses Power BI to follow the sales performance by store. Each store has a weekly target, and the manager can quickly determine which stores did or did not meet their goals by applying conditional formatting:

• Green for stores with sales ≥ target
• Red for sales < target

The manager effectively used a table visual and a simple DAX measure to provide colour logic to represent the raw data visually, which represents its status, enabling faster decision-making, finding patterns, and sharing simple insights with upper management during team meetings.

Performance Consideration

• Apply Formatting to aggregated field values like sums, averages, etc.
• Avoid using complicated measures, as they can create performance issues.
• Avoid applying formatting to a large table, as it can affect the responsiveness of the table, so make sure to apply formatting to only the TOP N values or data that is filtered.
• Heavy formatting can lead to delays when working with a live connection or a direct query, so try to use import mode where possible.

Best Practices

• Avoid using too many colours, which makes visuals more difficult to read. Only highlight the important points.
• Maintain the same colour meaning in all your visuals (e.g., red stands for negative, green stands for positive).
• Choose colour schemes that are readable for everyone.
• Make sure your visuals are readable in both dark and light environments.

Conclusion

Conditional formatting in Power BI is a powerful tool that helps users transform raw data into meaningful visuals. By applying colours based on specific rules, it helps in highlighting the trends. It is also used to simplify complex data, which makes it easier for users to interpret data. Following best practices and performance considerations helps to improve the clarity and effectiveness of reports and dashboards.

To learn more about Power BI and its functions, check out this Power BI Course and also explore Power BI Interview Questions prepared by industry experts

Conditional Formatting in Power BI- FAQs