Conditional Formatting in Power BI for Advanced Analytics

Conditional formatting in Power BI is a feature that allows users to highlight and draw attention to important data trends in their visualizations. By applying color-coded formatting to specific criteria, users (and other stakeholders) can quickly and easily identify patterns and outliers in their data sets.

In this guide, you’ll discover the benefits of the Power BI conditional formatting function, and how to apply a conditional format in a Power BI matrix/table.

Table of Contents:

1. What is Conditional Formatting in Power BI?
2. Why Use Advanced Conditional Formatting Techniques?
3. How to Apply Conditional Formatting in the Power BI Matrix/Table?
4. How to Create Visualizations in Power BI?
5. What are the Benefits of the Power BI Conditional Formatting Function?
6. Wrap Up

First…

What is Conditional Formatting in Power BI?

Definition: Conditional formatting in Power BI allows users to dynamically change the appearance of data in their visualizations based on specified conditions. This will help the user emphasize important information, trend analysis, and make reports more visually appealing and informative.

Why Use Advanced Conditional Formatting Techniques?

If you’re looking to get more work done, you should opt for advanced conditional formatting techniques in Power BI. For instance, you can use dynamic formatting based on relative rather than absolute values. To simultaneously highlight multiple criteria, apply multivariate formatting. You can also create conditional formatting rules based on data that is not directly included in the data visualizations.

How to Apply Conditional Formatting in the Power BI Matrix/Table?

Here are easy steps to help you apply conditional formatting in Power BI for a matrix (or table):

• Open the report in Power BI Desktop
• Create a Table (or Matrix)
• Select the Column or Data Cell
• Access the Formatting options
• Apply Conditional Formatting
• Choose a Formatting Rule
• Configure the Rule
• Define Rules
• Preview and Apply
• Repeat for Additional Formatting
• Save and Publish.

Example

Step-by-Step Guide to adding conditional formatting in Power BI to evaluate data to a Color by Rules:

Select “Rules” in the Format by field of the Background color (or Font color dialogue box) to format the cell background (or font color) according to the rules. In the “Based on field” section, select the field on which the formatting is based. The “Summarization” displays the field’s aggregate type.

Enter one or more value ranges and colors for each under Rules. There’s the “If” value condition, a value condition, and a color for each value range. Each value range’s cell backgrounds or fonts are colored with the specified color. Look out for the three rules in the example below:

If you choose Percent from the options, the rule boundaries will be specified as a percentage of the whole range of values from lowest to maximum. If the lowest data point was 100 and the highest was 400, the preceding rules would color anything under 200 green, anything between 200 and 300 yellow, and anything over 300 red.

The illustration table below has rules-based background color formatting dependent on the Percent column in the Affordability column:

How to Create Visualizations in Power BI?

Stage 1: Logging in to Power BI

• Log in to Power BI.
• Enter your email address and click the “Submit” button.

• You are redirected to your Microsoft account.
• Enter your password and click “Sign in“.

• You can choose whether to stay signed in.

• Once done, the Power BI home screen will open.

Stage 2: Creating a Data Set and Selecting the Data Set to Use in Your Chart

• Go to the left-side menu and click the “Create” button.
• Select “Paste or manually enter data“.

• We’ll use the sample data below for this example.

Total Cost	Company Type	Company Name	Expertise Categories	Expertise	Cost
Total Cost	Subcontractor	Skyline Contractors	Mechanical Installation	Plumbing & Heating	15456
Total Cost	Subcontractor	Skyline Contractors	Mechanical Installation	Mechanical Work	10159
Total Cost	Subcontractor	Onyx General Contractors	Mechanical Installation	Plumbing & Heating	18045
Total Cost	Subcontractor	Onyx General Contractors	Mechanical Installation	Mechanical Work	12695
Total Cost	Subcontractor	Living Well Remodeling	Mechanical Installation	Plumbing & Heating	14589
Total Cost	Subcontractor	Living Well Remodeling	Mechanical Installation	Welding	11456
Total Cost	Supplier	Power-up Builders	Raw Material	Cement	20561
Total Cost	Supplier	Power-up Builders	Raw Material	Steel	32456
Total Cost	Supplier	Five-star Construction	Raw Material	Bricks	10253
Total Cost	Supplier	Five-star Construction	Raw Material	Timber	9000

• Paste the data table above into the “Power Query” window. After that, select the “Create a dataset only” option.

• Navigate to the left-side menu, and click on the “Data Hub” option. Power BI will populate the data set list. If no data set has been created, you’ll get an error message.

• Choose the preferred data set to be used in creating the Sankey diagram. After that, Power BI will populate the screen as shown below:

• Click on the “Create a report” dropdown, and select “Start from scratch.”

• A Report Canvas similar to the one below appears on your screen.

Stage 3: Adding the Power BI Sankey Diagram Extension by ChartExpo

• To create the Sankey Diagram, you’ll need an add-in or Power BI visual from AppSource. Navigate to the right side of the Power BI dashboard, and open the Power BI Visualizations panel. Click the ellipsis symbol (…) to import the Power BI Sankey Diagram extension by ChartExpo.

• Select the “Get more visuals” option from the menu that opens.

• Enter “Sankey Diagram for Power BI by ChartExpo” in the highlighted search box. You’ll see the “Sankey Diagram for Power BI by ChartExpo” as shown in the image below.

• Click on the highlighted “Add” button.

• Power BI will add the “Sankey Diagram for Power BI by ChartExpo” icon in the visualization panel.

Stage 4: Drawing a Sankey Diagram with ChartExpo’s Power BI extension.

• Select the “Sankey Diagram for Power BI by ChartExpo” icon in the visualization panel. A window similar to the one below will open in the report section of your dashboard.

• You can resize the visual as needed. Next, navigate to the right side of the Power BI dashboard. You’ll see “Fields’ next to “Visualizations.”

• At this point, you’ll have to select the fields to use in the Sankey chart. To select the fields, follow the sequence below:
  • Total Cost
  • Company Type
  • Company Name
  • Expertise Categories
  • Expertise
  • Cost

• You’ll have to provide your email address or a ChartExpo license key.

Stage 5: Activate the ChartExpo Trial or Apply a Subscription Key

• Select the ChartExpo visual. You’ll see three icons below “Build Visual” in the Visualization panel.

• Select the middle icon, “Format visual.” The visual properties will be populated as shown below.

• To use the ChartExpo as a new user, you’ll have to enter your email address in the textbox under the “Trial Mode” section. The License key will be sent to your email address. To activate your 7-day trial, toggle “Enable Trial.”

• You’ll receive a welcome email from ChartExpo. The Sankey Diagram you create using the 7-day trial comes with the ChartExpo watermark.

• If you have a license key, enter it in the “ChartExpo License Key” textbox in the “License Settings” section. Slide the toggle switch next to “Enable License” to “On.”

• You’ll notice that the Sankey diagram doesn’t come with a watermark.

• Here’s how to add a Prefix (like the $ sign) with the numeric values in the chart. Expand the “Stats” properties and include the Prefix value.

• To add colors to each node, expand the “Level Colors” properties and select the colors.

• All changes will be automatically saved.

 

Insights

• At Level 1 (Total Cost), the procurement cost is $155K.
• At Level 2 (Company Type), out of the $155K cost, $82.4K (53.3%) was spent on subcontractors, while $72.3K (46.7%) was allocated to the supplier.
• At Level 3 (Company Name), the supplier cost of $72.3K was divided between two companies: Five-star Construction and Power-up Builder, with charges of $19.3K and $53.0K, respectively.
• The subcontractor cost of $82.4K was distributed among three companies: Onyx General Contractors, Skyline Contractors, and Living Well Remodeling. They charged $30.7K, $25.6K, and $26.0K, respectively, for their services.

What are the Benefits of the Power BI Conditional Formatting Function?

• Improved Data Interpretation: Conditional formatting helps you emphasize important data points. That makes it easy for users to interpret the data and identify patterns (or outliers).
• Increased Visibility: With conditional formatting, you can draw attention to specific data points (or trends) using visual cues like best colors for graphs and icons. This will, in turn, increase the visibility of important information in the report.
• Customization for Impactful Reports: Power BI’s conditional formatting options enable you to customize the visual appearance of your reports, and that makes them more impactful and engaging.
• Time Savings: Instead of manually creating separate visualizations for different scenarios (or thresholds), conditional formatting automates the process. This saves time in report creation and maintenance.

FAQs

How do you add conditional formatting in Power BI measure?

To add conditional formatting in Power BI to a measure, select the measure, go to the “Format” pane, navigate to “Conditional formatting,” and configure rules for visual representation based on values.

How to do conditional formatting in DAX?

In DAX (Data Analysis Expressions), use the FORMAT function to apply conditional formatting. Define logical conditions within the function to format measure outputs based on specified rules or criteria.

Wrap Up

Conditional formatting in Power BI allows users to visually enhance data representation by applying formatting rules based on values. It is crucial for highlighting patterns, anomalies, and trends. It also makes data interpretation more intuitive and facilitates informed decision-making.

In Power BI, conditional formatting can be applied in color scales, data bars, icon sets, and rules-based formatting. Color scales can be used to assign different colors to data points based on their values. Data bars are horizontal bars that are added to a field to represent its value visually.

Icon sets allow you to display icons (like flags, arrows, or traffic lights) next to the data points. Finally, rules-based formatting helps in defining custom rules to format data based on certain criteria.

By following the steps outlined in this guide, you’ll be able to create compelling visualizations while incorporating conditional formatting into the entire data analysis process.