How to add visual insight to data by applying conditional formatting to a table or matrix visualization in Microsoft Power BI
Your email has been sent
Conditional formatting is an easy way to expose detailed insights. Learn more in this Power BI tutorial.
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 Microsoft’s 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.
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.
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.
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.
From the hottest programming languages to commentary on the Linux OS, get the developer and open source news and tips you need to know. Delivered Tuesdays and Thursdays
From the hottest programming languages to commentary on the Linux OS, get the developer and open source news and tips you need to know. Delivered Tuesdays and Thursdays
How to add visual insight to data by applying conditional formatting to a table or matrix visualization in Microsoft Power BI
Your email has been sent
TechRepublic Premium content helps you solve your toughest IT issues and jump-start your career or next project.
This is a comprehensive list of the best AI art generators. Explore the advanced technology that transforms imagination into stunning artworks.
Find the perfect payroll service for your business without breaking the bank. Discover the top cheap payroll services, features, pricing and pros and cons.
Is NordVPN worth it? How much does it cost and is it safe to use? Read our NordVPN review to learn about pricing, features, security, and more.
Free project management software provides flexibility for managing projects without paying a cent. Check out our list of the top free project management tools.
Australian and New Zealand enterprises in the public cloud are facing pressure to optimize cloud strategies due to a growth in usage and expected future demand, including for artificial intelligence use cases.
The end of a year provides an ideal opportunity for revisiting the previous year’s goals and performance, reviewing personal and professional aspirations and prioritizing the upcoming year’s initiatives. But where to start? Tech pros can implement these seven planning steps from TechRepublic Premium to successfully set and track goals and subsequently prioritize initiatives. From the …
The role of a technical copywriter is recognized as a cornerstone in defining and conveying a company’s identity in the ever-evolving landscape of today’s business, where the online presence and narrative of a brand have outstanding value. This hiring kit from TechRepublic Premium provides an adjustable framework your business can use to find the right …
With artificial intelligence being more popular than free alcohol at a tech conference, it’s wise to stay informed about all things AI or even implement policies for its correct usage. This TechRepublic Premium pack provides readers with seven downloads for a bargain price. The bundle comprises two glossaries about AI and machine learning; three features …
A successful chief digital officer drives their organization’s digital transformation and creates value while rationalizing business processes and the customer experience. This hiring kit from TechRepublic Premium provides a workable framework you can use to find the best CDO for your organization. From the hiring kit: EDUCATION AND EXPERIENCE Candidates must have a degree in …
Get the web’s best business technology news, tutorials, reviews, trends, and analysis—in your inbox. Let’s start with the basics.
* – indicates required fields
Lost your password? Request a new password
Please enter your email adress. You will receive an email message with instructions on how to reset your password.
Check your email for a password reset link. If you didn’t receive an email don’t forgot to check your spam folder, otherwise contact support.
This will help us provide you with customized content.
Thanks for signing up! Keep an eye out for a confirmation email from our team. To ensure any newsletters you subscribed to hit your inbox, make sure to add [email protected] to your contacts list.
Leave a Reply