How to Apply Conditional Formatting in Microsoft Power BI

Edited 1 week ago by ExtremeHow Editorial Team

Microsoft Power BIData VisualizationWindowsMacStylingFormattingReportsBusiness IntelligenceCustomization

Microsoft Power BI is a business intelligence tool that allows you to transform raw data into informative visuals. One of the features that enhances its analytical capability is conditional formatting. Conditional formatting is used to change the appearance of data in your reports or dashboards based on certain conditions. This feature helps to highlight trends, draw attention to anomalies, and improve data visualization.

Applying conditional formatting in Power BI involves defining rules to change visual elements such as colors, data bars, and icons based on the underlying data. This process can substantially improve your data storytelling by emphasizing specific data points.

Understanding conditional formatting

Before understanding how to apply conditional formatting, it is important to understand what it means and how it can be used to its full potential. Conditional formatting in Power BI can be applied to:

• Background color: The background color of a cell or data point can change depending on the value it contains.
• Font color: Like background colors, font colors can also be set to change.
• Data bar: A visual bar can be added within a cell, showing the cell's value relative to other cells.
• Icons: Various icons can be used to represent data, each icon corresponding to a different value or state.

Steps to apply conditional formatting

Create or open a Power BI report

To apply conditional formatting, you will first need a Power BI report. You can either create a new report or open an existing report. When working with Power BI Desktop, you will have more advanced features.

Select a visual in a report

Select the visual to which you want to apply conditional formatting. This can be a table, matrix, or chart. The type of visual will determine the formatting options available to you.

Access conditional formatting options

Once your visual is selected, you'll want to access the conditional formatting options. To do this:

• Look at the Visualization pane on the right.
• Click the paint roller icon to open the Format pane.
• Locate the item you want to format under the Values section in the "Visualization" pane.
• Click the dropdown arrow next to it and choose Conditional Formatting.

Choose the type of conditional formatting

Power BI offers several options for conditional formatting:

1. Color scale

The Color Scale option allows you to set gradient colors based on field values. You can choose a minimum and maximum value, and the colors will automatically adjust between the two.

• Click the background color scale for the field you want to format.
• Select the minimum, maximum, and center values if necessary. Specify the colors for these values.
• Alternatively, diverging colours can also be selected to highlight the mid-points.

2. Rule-based formatting

Rule-based formatting allows you to apply different formats based on your specified rules that work on numeric or non-numeric fields.

• After clicking the dropdown for conditional formatting, select Rules.
• Add rules by specifying conditions and corresponding formatting.
• For example, if you want the background color of a cell to be green if the sales figure is greater than ten million, and red otherwise, you can define that rule here.

3. Field values

This option allows you to apply formatting based on the values of another field, which is often useful when your dataset includes pre-defined visualization fields.

• After opening the conditional formatting settings for your field, select Based on field.
• Choose a format based on another field from the dropdown list.

Conditional formatting examples

Example of a color scale

Consider a sales report that has a column for monthly sales figures. Using a color scale, you can set cells with lower sales to red and cells with higher sales to green, mixing colors for mid-range values.

• Go to the Format pane, choose Conditional Formatting, and then choose Background Color Scale.
• Set the minimum selling price to as low as ≤ #FF0000 (red).
• Set the maximum to highest selling price ≥ #00FF00 (green).

Example of rule based formatting

Let's say you're creating a performance review dashboard, and you want to highlight entries where employee performance scores are considered "excellent."

1. Select your table or matrix visual, and navigate to the field you want to format.
2. Select Conditional Formatting, and then select Rules.
3. Create a rule like: If performance score is ≥ 90, then the font color should be blue or any other color of your choice.

Benefits of using conditional formatting

Using conditional formatting effectively in Power BI provides several benefits:

• Better data interpretation: By using visual indicators like colors and icons, you make the data easier to understand at a glance.
• Advanced insights: Easy identification of trends, outliers, and important data with visual representation makes insight discovery much simpler.
• Customizable visualizations: Endless possibilities for customization to suit the specific needs and characteristics of your data.
• Time savings: Automatic visual cues eliminate the need to manually find or calculate important data points.

Tips for effective conditional formatting

Although conditional formatting is an invaluable tool, when used excessively or incorrectly, it can reduce the overall effectiveness of your report.

• Don't overuse it: Use conditional formatting sparingly, applying it only where it has a meaningful impact or highlights important metrics.
• Choose different colors: Choose colors that are easily distinguishable to package data viewers and avoid using multiple shades of the same color.
• Consistent usage: When creating multiple reports or dashboards, keep the conditional formatting consistent to avoid confusing users.

Conclusion

Conditional formatting, when used correctly, can greatly improve the informativeness and attractiveness of your Power BI reports and dashboards. It transforms raw data into easily consumable insights using visual cues that highlight important areas. Understanding the different types of conditional formatting and applying them appropriately helps you unlock their full potential.

Make sure you stay aware of the audience's needs and expectations. Keeping consistency and clarity as a top priority will make your Power BI visualizations both effective and practical. Enjoy data visualization!

  Share This Article

If you find anything wrong with the article content, you can request an update