Power BI Matrix: A Comprehensive Guide

Matrix vs. Other Kinds of Tables 

You may be aware of other kinds of tables if you have worked with products in the Microsoft ecosystem. The Power BI matrix distinguishes itself from other kinds of tables like the Power BI table and the Excel pivot table. While the pivot table is adept at handling detailed data manipulations within Excel, it lacks the seamless integration with real-time data updates and data sources that Power BI offers. On the other hand, although the Power BI table exists in Power BI, it does not have features like expandable rows and columns.

Let’s document the differences in a table of our own. 

Power BI matrix vs. other tables

When to Use a Power BI Matrix 

Learning how to choose the right visualization, given our objectives, takes some time and practice. Luckily, we can follow a few heuristics that help us understand when a matrix is ideal. Let's take a look. 

Comparative analysis

The first point is that a matrix is suitable for comparative analysis. The grid format of a matrix allows us to see how different groups compare, correlate, or how they might vary. In our example, we see that Profit Margin is highest in Germany and lowest in the United States. 

Multiple dimensions

A matrix is also a great option if our analysis involves two or more dimensions. In effect, we use a matrix to find a way to include even more information. In our example, we consider the Profit Margin for different countries over time, which is three dimensions in a single cell. 

Hierarchies

A matrix is a great option if the relationships in our dataset take a nested structure. The matrix is perfect for data with natural hierarchies because it allows users to expand and collapse levels without losing the overall context.In our example, the United States contained several levels, such as Channel Partners and Enterprise. Importantly, we are confident that the values for our subcategories add up to the total in the top level. 

Need for subtotals and grand totals

A matrix is a good choice when we want to keep the total values for categories and subcategories in mind. The Power Bi matrix even dynamically recalculates numbers when we use features to drill down and filter. In our example, we see a Total row at the bottom, which provides a good reference point as we compare categories.

Cross-highlighting with other visuals

A matrix is a good choice in reports where multiple visuals need to interact. Selecting elements within a matrix will automatically highlight or filter related data in other charts, making the report more interactive.Check out our tutorial on designing engaging Power BI reports for practical tips and best practices for creating impactful reports and dashboards.

Creating a Basic Matrix in Two Steps

Let's explore how to create the Power BI matrix in the visual below. We will first create a basic matrix in two easy steps. We will then customize our matrix using the more advanced features available in Power BI.

Power BI matrix example. Image by Author

Step 1: Add the matrix visual to your report

As a first step, we have to create our matrix:

• We go to the Report view.
• We go to the Visualization pane.
• We select the Matrix icon.

This will create an empty matrix box on our report canvas. 

Empty Power BI matrix. Image by Author

Step 2: Add data to the matrix

For our second step, we choose fields for rows, columns, and values:

• Rows will determine what each row in the matrix represents.
• Columns will determine what each column in the matrix represents.
• Values refer to a measure of some kind. It is a custom aggregation based on our selected rows and columns.

Adding matrix fields. Image by Author

After creating a blank matrix box and choosing the correct fields for rows, columns, and values, we now have our basic Power BI matrix. 

Power BI matrix with data included. Image by Author

Customizing A Power BI Matrix

Now that we have created our Power BI matrix, it’s time to customize the matrix to make it stand out. There are many options to customize our matrix according to our report requirements, and we can get quite creative:

• Conditional formatting makes data points stand out. 
• Totals and subtotals anchor our tables in a larger context. 
• Tooltips provide additional context when hovering over items. 
• Styles, borders, and grids make the matrix look nice.
• Expand and collapse icons help us manage the level of detail. 
• Drill through allows us to navigate to a report page by clicking.  
• Sparklines create line graphs inside the matrix cells. 

Let’s explore the full range of Power BI capabilities!

Conditional formatting

Trends, patterns, or anomalies can easily get lost in a sea of numbers. We can make the important parts of our data stand out using conditional formatting by following these steps.

• Click on the matrix.
• Go to the Format section. 
• Expand Cell elements. 
• Choose a background and font color. 
• Add data bars, icons, or a web URL.

Power BI conditional formatting pane. Image by Author

Totals and subtotals

Control how you summarize data in a matrix by choosing the placement of subtotals and totals.

• Click on the matrix.
• Go to the Format section. 
• Navigate to Subtotals.
• Configure Subtotals Settings.
• Toggle the visibility of subtotals on or off.
• Choose whether subtotals appear at the top or bottom of the groups.

Power BI totals and subtotals pane. Image by Author

A good practice is to keep subtotals at the top of groups for easy visibility when scrolling through large datasets.

Tooltips

Tooltips is a useful feature that allows us to provide additional information without cluttering the matrix.

• Click Enable tooltips.
• Set the type to Report page.
• Enable the Allow use as tooltip option 
• Set the canvas size to Tooltip.

Power BI tooltips pane. Image by Author

Power BI tooltips visualizations pane. Image by Author

Here is an example of a tooltip showing profit margin by product. The tooltip dynamically filters based on the year, country, or segment for the chosen cell in the matrix.

Power BI report with tooltips included. Image by Author

Tip: Try to include only the most relevant information in tooltips. Don’t overdo it, or else it will cause more clutter.

Styles, borders, and grids

Using grid lines and borders to separate rows and columns, or choosing different styles, can enhance the readability of our matrix. This is particularly helpful when dealing with large amounts of data. 

For gridlines, we follow these steps:

• Click on the matrix.
• Go to the Format section. 
• Click to expand the Grid section.
• Choose Horizontal gridlines or Vertical gridlines.

Note: We can opt for lighter gridlines to avoid visual clutter while maintaining cell separation.

Power BI gridlines pane. Image by Author

For headers, we follow these steps:

• Click on the matrix.
• Go to the Format section.
• Click Row headers or Column headers.
• Enable the Stepped layout.

Note: The preset None is generally the cleanest option for headers unless we have precise branding and color requirements.

Power BI headers pane. Image by Author

Expand and collapse icons

The expand and collapse icons are another recommended step to manage how data is presented. 

• Go to the Row headers option. 
• Enable the (+/-) icons.
• Change the color and adjust the size.

Power BI expand and collapse pane. Image by Author

Note: All dimensions are displayed within a single column in the hierarchy by default. We have the option to maintain this stepped layout or configure each level to expand into a new column, as shown in the following example. 

Power BI stepped layout example. Image by Author

Drill through

The drill through feature allows us to click on elements within a matrix and navigate to another report. This page retains the filters from our selection, which saves us time by eliminating the need to reapply our filter again.

• Prepare the Drill through page.
• Go to the Fields pane. 
• Go to the Drill through at the bottom of the Fields pane. 
• Ensure that the fields used in the drill through filters are present in the matrix.

Drill through page. Image by Author

Power BI report with drill through enabled. Image by Author

Data bars & sparklines

The matrix visual can be enhanced with two types of miniature visuals: data bars and sparklines. Data bars are used for conditional formatting. 

• Right-click the Values field.
• Select Conditional formatting.
• Select data bars.
• Adjust the appearance of the bars in the dialog box. 

Sparklines are small line graphs that show data trends directly in the matrix.

• Right-click the Values section.
• Select Add a sparkline.
• Choose the X-axis or Y-axis option in the dialog box. 

Adding a sparkline. Image by Author

Sparkline dialog box. Image by Author

If you want to learn more about data bars in Power BI, check out our conditional formatting in Power BI course.

5 Common Pitfalls and How to Avoid Them

Now that we have created our Power BI matrix, let's take a beat to learn how to avoid common mistakes that might hinder the effectiveness of our matrix.

1. Overloading data

Crowding too many rows, columns, and values into a single matrix can make it challenging to interpret. Focus on key metrics and dimensions. Utilize drill down features to allow users to explore data levels without initial overload. Consider using slicers or filters to manage how much data is shown in one view.

2. Poor performance with large datasets

Matrices can become slow and unresponsive when handling large datasets due to excessive calculations and rendering. Optimize your data model by reducing the number of unnecessary columns and rows. Do some housekeeping to eliminate calculations you aren’t using in your final product. 

Check out our Data Modeling in Power BI tutorial for best practices when designing your data model.

3. Ineffective use of conditional formatting

Use conditional formatting judiciously. Choose color schemes that are easy on the eyes and logically enhance the data’s readability. Focus on highlighting key data points or trends, and avoid using too many colors or rules.

4. Complex hierarchies

Maintain a simple hierarchy where possible. Start with a collapsed view and allow users to expand for more details. Clearly label each level of the hierarchy to make it easier for navigation.

5. Ignoring mobile users

If you have mobile users, design your reports with mobile in mind. Test how your matrix looks and behaves on different devices. Use features like responsive layout design in Power BI to ensure that matrices are legible on mobile devices.

Conclusion

The Power BI matrix is an incredible tool for effectively organizing and analyzing data. And with a bit of consistent practice, the Power BI matrix will significantly enhance your ability to create impactful reports. Use features like conditional formatting, drill through, sparklines, and data bars to transform your matrix into something especially dynamic and interesting. Take our Data Analyst in Power BI career track to learn more data visualization techniques, get certified, and land a job as a Power BI Developer. And keep applying your skills by doing projects with real-world datasets. Check out our resource 8 Power BI Projects to Develop Your Skills to get started today.