How to Visualize Data in a Matrix in Power BI (Easy Guide)

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

A lot of people has been asking a simple question about Power BI:

Is there a visualization that shows data in different dimensions? Like a pivot table in Excel?

That’s where the matrix visualization in Power BI comes in!

In this tutorial, you’ll learn about visualizing data using a matrix table in Power BI.

Kasper Langmann, Co-founder of Spreadsheeto

Curious? Let’s get into it! 😊

*This tutorial is for Power BI Online (also called "Power BI Service"). If you have Power BI Desktop, then click here and go to the online version.

Introduction to Power BI’s Matrix Visual

The matrix visual is Power BI’s version of the pivot table.

The ordinary table in Power BI is only two-dimensional. The data shown are flat and duplicate values are displayed, not aggregated.

A matrix table supports multiple dimensions, a stepped layout, aggregates data, and has a drill-down functionality.

Like the ordinary table, creating a matrix table in Power BI is super easy.

In fact, there are only 3 buckets you have to fill in:

  • Rows
  • Columns
  • Values

In the next part, you’ll learn how to create a matrix visual in Power BI step by step.

Kasper Langmann, Co-founder of Spreadsheeto

Creating a Matrix Visual in Power BI

For this tutorial, we’ll be using the free retail analysis sample.

To be able to follow this tutorial closely, connect your Power BI with the sample dataset.

Kasper Langmann, Co-founder of Spreadsheeto

Let’s say that we’re creating a matrix table with the following elements:

  • This year sales
  • Store chain
  • Store territory
  • Item category

Now, we would like to place:

  • Store chains and territories as rows
  • Item categories as columns
  • Value of this year’s sales

First, click the matrix table icon on the visualizations pane.

Then, drag the appropriate fields to the right buckets.

Placing the right fields to the right buckets on a matrix table

Congratulations! You just made your first functional matrix table! Easy. 😊

Here’s how it looks on expanded view:

How an expanded matrix table looks like

Drill Down Functionality

Power BI’s matrix visual also has a nice drill-down functionality.

You can drill down using rows, columns, individual sections, and even on cells.

To drill down using rows and columns, you should first add multiple fields to the row and/or column buckets on the visualization pane.

Kasper Langmann, Co-founder of Spreadsheeto

In a way, this creates a hierarchy or grouping which allows you to drill down on that hierarchy.

For example, in the matrix table we made, we put two fields on the row bucket:

  • Chain
  • Category

You would know if the matrix table has hierarchy since you will see drill down/up icons on the upper-left corner.

Presence of hierarchy enables drill down/up buttons on the upper-left corner of the matrix table

In addition, you could right-click on the elements of the row or column with hierarchy and see if you can expand or collapse the hierarchy of that element.

If you would like to go deeper, you could also select ‘Show Next Level’ to see the values under the lower-level row.

For example, right-clicking on the ‘Fashions Direct’ chain and choosingShow Next Level’ would show the sales this year according to category (column) and territory (row).

Clicking the ‘Show Next Level’ would show you to the next hierarchy

Cool! 😊

Formatting Options

Power BI has a lot of formatting options for the matrix visual.

To view them, clickFormat’ on the visualizations pane while the visual is selected.

Some of them that are unique to this visual include:

  • Grid
  • Column headers
  • Row headers
  • Values
  • Subtotals
  • Grand total
  • Field formatting
  • Conditional formatting

Some of them have interesting effects on the table.

Like in conditional formatting, you could show data bars on the table that corresponds according to the value of that cell.

Here’s how it looks:

Data bars on a matrix visual

Looks good! Feel free to try any of the formatting options and see how each one affects the matrix table.

Kasper Langmann, Co-founder of Spreadsheeto

Wrapping things up…

A matrix is a great way to show your data in different dimensions. As you can see, it has everything that a normal table visual can do plus the advanced functionalities it brings like drill-downs and conditional formatting.

However, you should take into account that a matrix visual might not be the best for presentations. It’s always better to show only the specifics and the most important data.

Kasper Langmann, Co-founder of Spreadsheeto