Create a Matrix in Power BI

A Power BI Matrix is similar to a table that contains rows and columns. However, the matrix shows the aggregated data instead of displaying the detailed rows. The aggregation of the numeric measure depends on the column and row headers. If you add a hierarchy to column or row headers, you can drill up and down based on the levels.

When to use a matrix

  • To display the aggregated information in tabular format.
  • To perform row and column grouping.
  • To view row and column subtotals, totals, and grand totals.
  • To enable drill down action across the report.

Power BI Matrix, called the Cross tab, helps display the relationships between two or more groups. Let me show you how to Create a Matrix with an example.

We will use the SQL Data Source we created in our previous article for this Power BI Matrix demonstration. So, Please refer to Connect to SQL Server article to understand the Power BI Data Source.

How to Create a Matrix in Power BI

When you drag any dimension field to the Canvas, it automatically creates a table. Let me drag and drop the English Product Name from the Fields section to Canvas. And you can see the table.

Drag Dimension Field to Values 1

Next, drag one metric value (Sales Amount) to the Values section and click on the Matrix under the Visualization section.

Add Values 2

Clicking the button will automatically convert a Table into a Power BI Matrix. For example, the screenshot below shows the cross tab representation of English Product names and Sales Amounts.

Add Rows and Values 3

Let me add one more column group so that you can understand the cross tab. As you can see, we added Customer gender to the Column Groups.

Add Field to Column 4

Create a cross tab in Power BI Approach 2

You can follow this step if you want to convert the existing chart into Power BI Matrix.

As you can see from the screenshot below, we created a column chart representing the Order Quantity by English Product Subcategory.

Create a Matrix in Power BI 5

Click on the below-shown icon under the Visualization section automatically convert the Column Chart into a cross tab.

Create a Matrix in Power BI 6

Let me add the Product Color to the Column Group

add color field to column group 7

Approach 3

To create it, First, click on the Power BI Matrix present in the Visualization section. It automatically creates a cross tab with dummy data. Next, let me add the product Color as the Row Group.

Add Color to a Row section 8

Next, drag and drop the English Occupation into the Column Groups. Now, you can see the cross tab.

Add Dimension and Measure a Matrix in Power BI 9

Next, let me drag the Sales Amount to the Values section. It automatically enables the Subtotals and grand totals.

Add Sales Measure to Values 10

For this demonstration, we are adding one more metric field, i.e., Total Product Cost, to the Values section.

Add another measure to values 11

Let me do some quick formatting to this Power BI Matrix

NOTE: I suggest you refer to Format a Cross Tab article to understand the formatting options.

Create a Matrix in Power BI 12

Matrix FAQ’s

What is the difference between a table and a matrix in Power BI?

A table displays the data in a two-dimensional format. So, it shows detailed information and duplicates records.
A matrix displays the data in a layered or stepped layout. And the important aspect is that the matrix automatically aggregates the measure (numeric) fields. The second one is that it enables a drill down approach.

How do you show a matrix in Power BI?

Under the Visualization section, Selecting the Matrix icon will generate a dummy Matrix. Next, add dimensions to both Rows and Columns section and the Numeric measure to values. Apart from this, you can convert an existing table to a matrix.

How to create a drill down Matrix report?

Add Hierarchy to rows section creates a drill down action for rows. Whereas adding a hierarchy to columns enables the column-wise drill down action.

About Suresh

Suresh is the founder of TutorialGateway and a freelance software developer. He specialized in Designing and Developing Windows and Web applications. The experience he gained in Programming and BI integration, and reporting tools translates into this blog. You can find him on Facebook or Twitter.

Comments are closed.