How to Create a Matrix Table in Power BI

A matrix table in Power BI is less of a visualization and more of a data analysis superpower. If you've ever used a pivot table in Excel, you'll feel right at home with the matrix visual, but with far more power and interactivity. This guide will walk you through exactly how to create a matrix table, from the basics of dragging and dropping fields to customizing it for maximum impact.

What Exactly is a Power BI Matrix Table?

Think of a matrix as a smarter, more dynamic version of a standard table. While a regular table visual displays data in a simple two-dimensional format (rows and columns), a matrix allows you to create a grid-like structure with multiple levels of categories organized into hierarchies for both rows and columns.

Its closest cousin is the Excel PivotTable. You can summarize data by categories and subcategories, and it's built for slicing, dicing, and digging deeper into your numbers. The key function that makes a matrix so useful is its ability to drill down through different levels of your data hierarchy right within the visual itself. You can start with a high-level summary and expand sections to see the more granular details that make up the total.

Key Features of the Matrix Visual:

• Hierarchical Display: Show relationships between parent and child data points, like Country → State → City.
• Drill-Down and Drill-Up: Easily expand or collapse sections of your rows and columns to explore different levels of detail.
• Cross-Highlighting: Clicking on a value in your matrix can dynamically filter other visuals on your report page, making for an interactive analysis experience.
• Stepped Layout: Presents hierarchical data with clear indentation for subcategories, making the relationships easy to understand at a glance.
• Conditional Formatting: Add data bars, background colors, or icons based on the values in your cells to instantly spot trends, outliers, and key performance indicators (KPIs).

When Should You Use a Matrix Table?

The matrix visual shines when you need to display data across multiple dimensions or hierarchies. It’s the perfect choice whenever a simple table won't cut it because you need to group and summarize information in a more structured way.

Here are a few common scenarios where a matrix is the ideal visual:

• Sales Performance Analysis: You want to see total sales broken down by Salesperson (rows) and by Product Category (columns), and then be able to drill down to see performance by Product Sub-Category.
• Financial Reporting: You're building a profit and loss statement where the rows are your chart of accounts (e.g., Revenue, Cost of Goods Sold, Expenses) and the columns represent time periods (e.g., Year → Quarter → Month). A matrix makes this clean and easy to navigate.
• Inventory Management: You need to view inventory levels for products, grouped by Warehouse Location in the rows and by Product Status (e.g., In Stock, Low Stock, Out of Stock) in the columns.
• Web Analytics: You want to compare user sessions by Traffic Source in one dimension and by Device Category (Desktop, Mobile, Tablet) in another.

Essentially, if your goal is to compare measures across several different fields, a matrix table is your best friend.

How to Create a Matrix Table: A Step-by-Step Guide

Let's create a matrix from scratch. For this example, we'll use a simple sales dataset that contains fields like Region, Product Category, Sub-Category, and Sales Amount.

Step 1: Open Power BI and Add the Matrix Visual

First, open your report in Power BI Desktop. In the Visualizations pane on the right-hand side, find and click on the Matrix icon. Power BI will add an empty matrix placeholder to your report canvas.

Step 2: Define Your Rows

This is where you'll build the vertical structure of your table. From the Data pane, drag the fields you want to use as row headings into the Rows field well in the Visualizations pane.

The order you place them in matters, as it defines the hierarchy. For example, let's add:

• Region
• Product Category
• Sub-Category

At first, your matrix will only show the top level of the hierarchy (in this case, Region).

Step 3: Define Your Columns

Next, let's set up the horizontal headers. Drag a field that you want to pivot your data against into the Columns field well. This is optional, you can have a matrix with only rows and values, but adding columns unlocks its full power. For our example, let's drag over a date field and organize our data by year.

Step 4: Add Your Values

The matrix is just a skeleton without any numbers. Drag the numeric field you want to measure and summarize into the Values field well. This is typically a metric like sales, revenue, units sold, or costs.

Let's drag Sales Amount into the Values area. Power BI will automatically aggregate it as a sum. Now, your matrix will populate with the total sales amount for each region and year intersection.

Step 5: Drill Down to Explore Your Data

Now for the fun part. You’ll see small plus/minus icons (+) next to your row headers (e.g., next to "Northeast" region). This is the drill-down function.

• Click the '+' icon next to a row header (like a 'Region') to expand it and reveal the next level in your hierarchy ('Product Categories' within that region).
• You can also use the drill-down buttons at the top right of the visual to expand the entire hierarchy at once. These buttons allow you to "Go to the next level in the hierarchy" or "Expand all down one level in the hierarchy."
• Right-click on a row header to see options to drill down or drill up for more fine-tuned exploration.

Advanced Matrix Table Customization

Once you've built your basic matrix, you can refine its appearance and functionality to make your insights even clearer.

Applying Conditional Formatting

Highlight important data points without having to squint. Conditional formatting automatically changes a cell's appearance based on its value.

1. Select your matrix.
2. Go to the Format your visual pane (the paintbrush icon).
3. Expand the Cell elements section.
4. Find your value field (e.g., Sum of Sales Amount) and turn on options like Background color or Icons.
5. Click the fx button to set your rules. For example, you can use a color scale where lower sales are shaded red and higher sales are green. This instantly draws your eye to top or bottom performers.

Working with Stepped Layout

By default, the matrix uses a "stepped layout" for rows, where subcategories are indented under their parent category. This is generally great for readability.

However, you can turn this off for a more "flat" table view, where each field in the hierarchy gets its own column. To change this:

• In the Format your visual pane, go to Row headers.
• Find the Stepped layout toggle and switch it off.

You’ll notice that the indentation disappears, and you no longer see the +/- icons beside each row header. This can be useful for exporting data but removes the easy drill-down feature from the rows themselves.

Controlling Totals and Subtotals

Power BI automatically adds totals, but you can customize them.

1. In the Format your visual pane, open the Subtotals section.
2. Here, you can independently toggle Row subtotals and Column subtotals on or off. You can even choose to apply these settings "per row level" or "per column level" for more granular control.
3. Similarly, you can adjust the grand totals under the Grand total section.

Quick Tips for an Effective Matrix Table

• Less is More: Avoid cluttering your matrix with too many fields in both rows and columns. This can create a massive, hard-to-read table. Use Power BI Slicers to let users filter the data instead.
• Use Custom Measures with DAX: For calculations beyond simple sums or averages (like profit margin or year-over-year growth), create a DAX measure and drag that into the Values field. For example: Profit Margin = (SUM('Sales'[Sale]) - SUM('Sales'[COGS])) / SUM('Sales'[Sale])
• Check Your Data Relationships: The matrix relies on the relationships defined in your data model. Ensure your tables are linked correctly for accurate summaries and filtering.

Final Thoughts

The Power BI matrix table is a workhorse visual essential for anyone serious about creating detailed yet digestible reports. By mastering its hierarchical structure, drill-down capabilities, and formatting options, you can turn flat data tables into rich, interactive dashboards that empower users to find their own insights faster.

Of course, building the report is only half the battle. Often, the real struggle is connecting and unifying your data in the first place, especially across marketing and sales platforms. That's why we built Graphed. We automate the entire data connection and reporting process, letting you create dashboards from sources like Google Analytics, Shopify, and Salesforce simply by asking for what you need in plain English. You can get straight to analysis without the setup headaches.