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

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

How to add visual insight to data by applying conditional formatting to a table or matrix visualization in Microsoft Power BI – TechRepublic 0 0 Alan Dickson

Register for your free TechRepublic membership or if you are already a member, sign in using your preferred method below.
We recently updated our Terms and Conditions for TechRepublic Premium. By clicking continue, you agree to these updated terms.
Invalid email/username and password combination supplied.
An email has been sent to you with instructions on how to reset your password.
By registering, you agree to the Terms of Use and acknowledge the data practices outlined in the Privacy Policy.
You will also receive a complimentary subscription to TechRepublic’s News and Special Offers newsletter and the Top Story of the Day newsletter. You may unsubscribe from these newsletters at any time.
All fields are required. Username must be unique. Password must be a minimum of 6 characters and have any 3 of the 4 items: a number (0 through 9), a special character (such as !, $, #, %), an uppercase character (A through Z) or a lowercase (a through z) character (no spaces).
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.
Be your company’s Microsoft insider by reading these Windows and Office tips, tricks, and cheat sheets.
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
Your message has been sent
TechRepublic Premium content helps you solve your toughest IT issues and jump-start your career or next project.
Windows 11 gets an annual update on September 20 plus monthly extra features. In enterprises, IT can choose when to roll those out.
Edge AI offers opportunities for multiple applications. See what organizations are doing to incorporate it today and going forward.
This is a complete guide for Apple’s iPadOS. Find out more about iPadOS 16, supported devices, release dates and key features with our cheat sheet.
Discover data intelligence solutions for big data processing and automation. Read more to explore your options.
Whether you are a Microsoft Excel beginner or an advanced user, you’ll benefit from these step-by-step tutorials.
This document helps make sure that you address data governance practices for an efficient, comprehensive approach to data management. This checklist from TechRepublic Premium includes: an introduction to data governance, a data governance checklist and how to manage a data governance checklist. From this checklist’s introduction: Data governance is the process by which an organization …
Recruiting a Scrum Master with the right combination of technical expertise and experience will require a comprehensive screening process. This hiring kit provides a customizable framework your business can use to find, recruit and ultimately hire the right person for the job. This hiring kit from TechRepublic Premium includes a job description, sample interview questions …
Knowing the terminology associated with Web 3.0 is going to be vital to every IT administrator, developer, network engineer, manager and decision maker in business. This quick glossary will introduce and explain concepts and terms vital to understanding Web 3.0 and the technology that drives and supports it.
While the perfect color palette or the most sublime button shading or myriad of other design features play an important role in any product’s success, user interface design is not enough. Customer engagement and retention requires a strategic plan that attempts to measure, quantify and ultimately create a complete satisfying user experience on both an …

source

    Would you like to receive notifications on latest updates? No Yes