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.

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.

• 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.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

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.

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.

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.

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.

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.

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.

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

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.

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.

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.

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.

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.

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 #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.

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.

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.

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.

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

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.

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.

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.

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.

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

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.

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)

Recommended Articles

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

• Power BI IF Statement
• Power BI KPI
• Power BI Share Reports