How to add visual insight to data by applying conditional formatting to a table or matrix visualization in Microsoft Power BI

Designers often use table and matrix visualizations when summarizing numeric data because they’re effective. You see the actual values and grouping instead of bars and lines that might not make as much sense. Even then, depending on grouping, users still might struggle to find details that are important to them. Fortunately, you can add conditional formatting to both the table and matrix visualizations in Power BI to make some data stand out, and the results are dynamic.

In this tutorial, I’ll show you how to add conditional formatting to table and matrix visualizations to make specific data stand out in Power BI. The process is easy, and the results are helpful to the users.

SEE: Google Workspace vs. Microsoft 365: A side-by-side analysis w/checklist (TechRepublic Premium)

I’m using Microsoft Power BI Desktop on a Windows 10 64-bit system. You can download the demonstration .pbix file, AdventureWorks Sales from GitHub. Once downloaded, double-click the .pbix file to open it in Power BI and follow along or use a .pbix file of your own. If you want a sneak peek at the final results, check out this demo file.

How to apply conditional formatting to a table or matrix in Power BI

Power BI offers a helpful user interface for applying conditional formatting to a table or matrix visualization. Conditional formatting is dynamic, updating automatically as the data changes.

Figure A shows a simple matrix visualization that displays sales by product. It also has a drill hierarchy, that’s a result of a relationship between the tables; Power BI generates this hierarchy automatically. When applying this technique to your own work, be sure to check for the proper relationships between tables.

Figure A

To create this simple visualization, do the following:

1. Click the matrix visualization in the Visualizations pane.

2. Drag the Category field from the Product table to the Row bucket.

3. Drag the Country field from the Sales Territory table to the Row bucket, and position it below the Category field. You could rearrange these two fields, depending on your focus.

4. Drag the Sales Amount field from the Sales field to the Values bucket.

We want to see the lowest selling products by country. When grouping, Power BI will automatically sort the Sales Amount column, but that might not be enough. Specifically, we want to know when sales drop below a certain benchmark. Once you know that information, you can make decisions. Perhaps the company will stop offering those products in certain markets. Or maybe the company will put more energy into those markets.

Applying conditional formatting to highlight low sales values

Now, let’s apply a simple conditional formatting rule to the Sales Amount field that will highlight values that are less than $100,000.

1. In the Visualizations pane, find the Values bucket and click the Sales Amount dropdown.

2. Choose Conditional Formatting from the resulting menu.

3. Select a Background Color from the next submenu (Figure B).

Figure B

In the resulting dialog, you can express your condition.

1. From the Format Style dropdown, choose Rules. Apply To defaults to Values, which is what we want.

2. From the What Field Should We Base This On dropdown, choose Sales Amount from the Sales table. Power BI displays the choice as Sum of Sales Amount.

3. In the Rules section, the first two arguments are correct as is. Change the third control to Number. This rule will match all values that are greater than 0. That would match everything, but it is the correct lower boundary.

4. To the right of the AND operator, choose the <= quality operator. Enter 100000 in the second control. If the last dropdown doesn’t default to Number (it should), choose Number from that dropdown (Figure C).

Figure C

5. To the far right, choose red from the color dropdown.

6. Click OK.

When you return to the matrix, you might not see any difference because the values that match the conditional formatting rule are toward the bottom of the list. Use the scroll bar or double-click the Sales Amount header cell to flip the sort.

As you can see in Figure D, the rule exposes two records. With this information in hand, you can make decisions on the Germany market.

Figure D

This process was simple, and the payoff is large.

Applying conditional formatting to highlight the most profitable countries

Now, let’s suppose you want to see which product by country returns the greatest profit. This time let’s use a gradient scale instead.

Let’s add another column and add a gradient format as follows:

1. Expand the Fields pane if necessary and add Profit Amount from the Sales table to the Values bucket. Double-clicking the field should do so for you.

2. In the Visualizations pane, find the Values bucket, and click the Profit Amount dropdown.

3. Choose Conditional Formatting from the resulting menu, and then, select Background from the next submenu.

4. Choose Gradient from the Format Style dropdown.

5. Everything defaults perfectly (Figure E), so click OK instead without changing any settings.

Figure E

This format adds gradient shades to the Profit Amount values (Figure F). The higher the value, the darker the color, which is blue. The United States has the highest profit, but that might be because they also have the largest sales, so this result isn’t as helpful as the first. We’d need to compare costs with profits to get an accurate answer on this question, which we won’t do.

Figure F

Let’s add data bars to the Profit Amount column to expose a bit more information:

1. In the Visualizations pane, click the Profit Amount dropdown.

2. Choose Conditional Formatting, and then, choose Data Bars.

3. The default settings shown in Figure G are good, so click OK without making any changes.

Figure G

As you can see in Figure H, bikes are the best-selling product in all regions. Clothing and accessories are both performing poorly. Again, we don’t know the true profit margin. That would require dropping in a measure for a true comparison.

Figure H

We’ve applied three conditional formats to this visualization. Doing so is easy, and the information they expose is helpful. Most importantly, the conditional formats are dynamic.