Power BI Conditional Formatting

What is Conditional Formatting in Power BI?

Conditional Formatting in Power BI allows you to change the formatting of visuals based on specific conditions.  It can be applied to any text or data field with a color name, number, hex code, or URL links. Formatting can be anything, like specifying customized cell colors based on values or representing the field values in tables or matrices with data bars, active web links or KPI icons. It allows you to highlight important data or trends in your reports and make it easier for your audience to identify and interpret insights.

Key Takeaways
  • Conditional formatting is a powerful feature in Power BI that can help users better understand and analyze their data.
  • By applying formatting based on specific conditions, users can quickly identify patterns and trends that might otherwise be difficult to spot.
  • There are several different types of conditional formatting options in Power BI, including background color, font color, data bars, and icon sets, that can be customized to meet various of needs.

How to Apply Conditional Formatting in Power BI?

Here are the steps to apply conditional formatting in Power BI:

  • Select a Table or Matrix Visualization you want to format in Power BI.
  • Right-click on the field or select the down-arrow next to it and choose the Conditional formatting option in the Visualization pane.
Power BI Conditional Formatting Intro
  • Choose the type of Conditional formatting to apply, i.e., Background color, Font color, Icons, or Web URL to format the visual for your report. For each of these values you choose, a pop-up window will open where you can specify the conditions and rules around the formatting.

Below is the sample window for Font color Power BI conditional formatting.

Power BI Conditional Formatting - Font color

Examples

As you have seen, four different types of Power BI Conditional formatting are supported in Power BI. We will discuss some of these examples in the subsequent sections below.

Example #1 – Using Background Color

In this example, we will use the sales order data collected across various US states to create a report of the Top 10 US states by sales and apply conditional formatting using Background color.

The steps involved are provided below:

Step 1: Import the Orders dataset using Data Connection in Power BI Desktop.

Step 2: Select the fields from the Data pane and drag them into the canvas to create a visualization table.

Power BI Conditional Formatting - Example 1 - Step 2

Step 3: Select the Format visual tab by navigating to the Visualization pane, expand the Title section and specify the header text and adjust the Heading, Font, Text Color, and Background color.

Power BI Conditional Formatting - Example 1 - Step 3

Step 4: Select the visual, open the More options (…) from the upper right corner of the tile. Select Sort descending order, and sort by Sum of Sales. It will sort the data as per the Sales amount in descending order.

Power BI Conditional Formatting - Example 1 - Step 4

Step 5: Navigate to the Filter section and select the down-arrow icon next to the State field to expand the Filter card. Select the filter type as Top N with a value of 20 and specify the By value, i.e., Sum of Sales. It would filter the dataset to the top 20 records as per sales.

Power BI Conditional Formatting - Example 1 - Step 5

Step 6: Select the Table Visualization you want to format in Power BI. Right-click on the field or select the down arrow next to the field, choose the Conditional formatting option in the Visualization pane, and select the Background color option.

Power BI Conditional Formatting - Example 1 - Step 6

It will open a Background color pop-up window.

Step 7: Specify the format style, the field on which the formatting and the background colors will be applied. Here, we have selected the Gradient format for our visualization. Once you have provided the details, click on OK.

Power BI Conditional Formatting - Example 1 - Step 7

It will apply background color to the Sum of sales from top to bottom based on their values.

Power BI Conditional Formatting - Example 1 - Step 7 - Tables

Example #2 – Using Font Color

You can also apply conditional formatting using the font color option in the table or matrix in Power BI. In this example, we will create a report on S&P Benchmark returns highlighting the 1-year return with different colors using the font color option.

Step 1: Import the Mutual Fund Benchmark Monitor dataset into Power BI, select the fields from the dataset, and drag them to the report canvas to create a table visual.

Example 2 - Step 1

Step 2: Select the Format Visual tab by navigating to the Visualization pane, expand the Title section, and provide the header text and adjust the Heading, Font, Text Color, and Background color.

Example 2 - Step 2.jpg

Step 3: Navigate to the Filter section and select the down-arrow icon next to the State field to expand the Filter card. Select Advanced filtering as the Filter type, and only filter the dataset that contains the keyword S&P as we want to track the S&P benchmark returns.

Example 2 - Step 2.jpg

Step 4: Select a Table Visualization you want to format in Power BI. Right-click on the field or select the down-arrow next to the field and choose the Conditional formatting option in the Visualization pane and select the Font color option.

Example 2 - Step 4.jpg

Step 5: Specify the format style, field on which the formatting will be applied, and font colors to be applied. Here we have defined the rules for the font colors as we have selected the Rules format for our visualization. Once you have provided the details, click OK.

Example 2 - Step 5.jpg

Now, the font colors of the Sum of sales field values are aligned with different colors as per their values provided in Power BI conditional formatting rules.

Example 2 - Step 5 - S&P

Example #3 – Using Data Bars

Here, we will create a report of the Top 10 US states by profits and apply conditional formatting using data bars in excel. The steps involved are below:

Step 1: Import the Orders dataset in Power BI Desktop, Select the fields from the Data pane and drag them into the report canvas to create a visualization table.

Power BI Conditional Formatting - Example 3 - Step 1

Step 2: Select the Format visual tab by navigating to the Visualization pane, expand the Title section and provide the header text and adjust the Heading, Font, Text Color, and Background color.

Power BI Conditional Formatting - Example 3 - Step 2

Step 3: Select the visual, open the More options (…) from the upper right corner of the tile and select Sort descending order, and sort by Sum of Profit.

Power BI Conditional Formatting - Example 3 - Step 3

Step 4: Navigate to the Filter section and select the down-arrow icon next to the State field to expand the Filter card. Provide the filter type as Top N with a value of 20 and specify the By value, i.e., Sum of Profit. This would filter the dataset visual to only 20 records as per sales.

Power BI Conditional Formatting - Example 3 - Step 4

Step 5: Navigate to the Conditional formatting option in the Visualization pane and select the Data bars option.

Power BI Conditional Formatting - Example 3 - Step 5

It will open a Background color pop-up window.

Step 6: Specify the format style, the field on which the formatting is to be applied and the background colors. Here we have selected the Gradient format for our visualization.

Power BI Conditional Formatting - Example 3 - Step 6

Once you have provided the details, click on OK. It will add background colors to the visual. It is a Power BI conditional formatting based on measure.

Power BI Conditional Formatting - Example 3 - Step 6 - background color

Example #4 – Using Icon Sets

This example highlights the Power BI conditional formatting using icon sets on S&P Benchmark returns. It is like Example – 2; we created using Font color; however, we will use icon sets in excel here. Hence, Steps 1 to 3 defined in Example-2 have been repeated to create this visual.

Once you have created the visual, navigate to the Conditional formatting option in the Visualization pane and select the Icons option.

Example 4 - Step 1

Specify the format style, the field on which the formatting will be applied, Style icon to be applied, and then click on OK. Here we have defined the rules for the icons as per the 1-year return values.

Note: You can also use custom icons instead of default style icons in Power BI.

Example 4 - Step 1

Power BI will add style icons as per the rules defined in conditional formatting.

Example 4 - Step 3

Power BI supports different conditional formatting. It can be Power BI conditional formatting based on measure, Power Bi conditional formatting based on text, etc. You can also create conditional formatting based on another column in the same table or matrix by providing the field based on which formatting can be applied.

Example 4 - Step 4

Important Things to Note

  • Conditional formatting is applied at the visual level, so you can apply different formatting to different visuals in the same report.
  • You can use multiple conditions to apply more complex formatting rules.
  • Conditional formatting can be used with a wide range of visuals in Power BI, including tables, matrices, charts, and cards.
  • It can help you highlight important insights in your Power BI reports and make your data more accessible and understandable for your audience.
  • Power BI doesn’t support applying color-code text values to any color. However, you can create measures related to those text values and then apply formatting to these measures.

Frequently Asked Questions (FAQs)

1. Can you copy conditional formatting rules in Power BI?

Yes, you can copy conditional formatting rules in Power BI. For this, select the report and choose the control containing the conditional formatting rules you want to copy. Then click on Format Painter in the Clipboard group under the Home tab. Select the report and click on the control to which you want to copy the formatting rules.

2. Why is Power BI Conditional Formatting not working?

If the Power BI conditional formatting is not working, there could be several reasons. Here are some common issues and their solutions:

3. How to add custom icons in Power BI conditional formatting?

Power BI provides a few default icon styles that can be used in conditional formatting. However, you can add custom icons by
Once successfully imported, you can see the imported icons by navigating to the conditional formatting window.

4. Can we apply conditional formatting on subtotals in Power BI?

You can apply conditional formatting rules on both totals and subtotals in Power BI for tables and Matrix visualizations. Select the Values and totals or Totals only from the Apply to drop-down as shown below.

This has been a guide to Power BI Conditional FormattingHere we learn how to apply conditional formatting in Power BI with examples & downloadable template. You can learn more from the following articles –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *