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 in Power BI 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 the Connect to SQL Server and the List of Charts articles 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 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 Power BI Matrix or cross tab report using multiple columns

You can follow this step if you want to convert the existing chart into a 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 to 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

Create a Matrix in Power BI Approach 3

To create it, click on the Power BI Matrix 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 add 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.

Create a Matrix in Power BI 12

Power BI Matrix Formatting

How to Format Power BI Matrix report with an example? The Power BI Formatting Matrix includes changing the Matrix Grid Colors, Row formatting, Column formatting, row and column colors, Matrix Title text, background color, etc.

To demonstrate these Power BI matrix formatting options, we will use the Matrix we created earlier. Please click on the Format button to see the Matrix formatting options available in Power BI.

Format Crosstab 1

Format Power BI Matrix General Settings

Use this General Section to Change the X, Y position, Width, and height of a Matrix.

Format Crosstab 2

Format Power BI Matrix Style

From the below screenshot, you can see the list of styles that are available to style this Matrix. Please select any style as per your requirement.

Format Crosstab 3

We are now selecting the Alternating Rows option from the drop-down list. It adds alternative row colors and Black color to the Header, Subtotals, and Grand totals.

Format Matrix 4

The following options are available for you to format the Power BI Matrix Grid.

  • Vert Grid: Add Vertical Grid to the Matrix by toggling this property from Off to On—nothing but enabling Vertical line to separate each column.
  • Horiz Grid: Add a Horizontal Grid—nothing but enabling a Horizontal line to separate each row.
  • Horiz Grid Color: Changing the Color of a Matrix Horizontal Grid.
  • Horiz Grid thickness: Matrix Horizontal Grid Line width
  • Row Padding: Space between the rows.
Format Crosstab 5

As you can see from the screenshot below, we changed the grid color to gold, grid thickness to 2, and row padding to 10

  • Outline Color: Color after the Header and row group. We are using the default green as the Outline color.
  • Outline Weight: Matrix Line width. We changed the line stroke to 4
  • Text Size: Font size of table values. Let me change the text size to 9
Format Matrix 6

As you can see from the screenshot below, we enabled the Vertical Grid and assigned some random colors.

  • Vert Grid Color: Change the Color of a Matrix Vertical Grid.
  • Vert Grid thickness: Matrix Vertical Grid Line width or stroke
Format Matrix 7

We renamed the column names under the values section to adjust the space. As you can see, we changed the Sales Amount names from Sales and Total Product Cost to Cost.

Format Crosstab 8

Format Column Headers of a Matrix in Power BI

This section is to alter the Header Colors. As you can see from the screenshot below, we changed the Font color to White and the background color to green. Next, the Font Family to Cambria, Outline to Top + Bottom (Outline added to the top and bottom of a Header), Text Size to 15, and Header text alignment to center.

Format Column Headers of a Matrix

Format Row Headers of a Matrix in Power BI

This section is used to alter or format the Row Headers section. Here, we changed the Font color to White, the Background color to Red, the Outline to Top + Bottom (Outline added to the top and bottom of each row), the Font Family to Cambria, the Text Size to 15, and the text alignment to center.

Format Row Headers of a Matrix

As you can see from the above screenshot, though, we changed the Background color of the total rows (Row Headers), and only the Total background color has changed. It is because of the Banded Row Style property that we explain in the next step.

Format Values of a Matrix

You can use this section to change the Power BI Matrix values. As you can see from the screenshot below, we changed the Font colors and Back colors of the even and odd rows.

Format Values of a Matrix

Banded Row Style: By default, it is set to On. It means the Row Header color will be the same as the remaining Row fields.

Let me set this Off. It changes the Row Header Background Color to Purple. It is because we applied this color in the above step, i.e., the Row Headers section.

Format Power BI Matrix 12

Show On Rows: Enabling this option shows the totals on Rows

Show on Rows

As you can see, we removed the Outline, Changed the Font to Cambria, and changed the Text size to 13

Format Power BI Matrix 14

Format Subtotals of a Matrix in Power BI

Use this subtotal section to alter the text and background color of subtotals. By toggling Row subtotals and column subtotals to Off, you can remove the Totals completely.

Format Subtotals of a Matrix

Format Grand Total of a Matrix in Power BI

Use this Grand Total section to alter Total’s text and background color. As you can see from the screenshot below, we changed the Totals Font color to white, the Background color to purple, the Font Family to Cambria, and the Text Size to 14.

Format Power BI Matrix Grand Total 16

Apply to labels: By toggling this option to On, apply the Background Color to the Header of the Total. As you can see, Power BI has changed the background color of the text of the header of Total.

Apply to Labels option in Power BI Matrix 17

Power BI Matrix Field Formatting

Use this section to format individual columns in a Matrix.

Field Format in Power BI Matrix 18

For example, you can use this section to assign a completely different color to one column. Here, we are selecting Sales.

Matrix Field Formatting 19

For the Format Power BI Matrix demo purpose, we only changed the background color to Pink for this column.

Format Power BI Matrix 20

Format Power BI Matrix – Conditional Formatting

Use this section to format Metric columns (Numeric) in a Matrix. For example, adding a background color to a complete column, adding data bars, etc. We will write a separate article to explain these settings.

Conditional Formatting in Power BI Matrix 21

Format Power BI Matrix Title

You can enable the Matrix title by toggling the Matrix option from Off to On. From the screenshot below, you can see we added the Title Text as Sales Vs. Total Product Cost by Product Color and Occupation.

Next, we changed the Font Color to Green, the Font Family to Georgia, the Text Size to 25, and the Title Alignment to center. You can add the background Color to the Matrix title if you want.

Format Power BI Matrix Title

Format Power BI Matrix Background Color, and Borders

You can add a Background color to a Matrix by toggling the Background option to On. For the Format Matrix demonstration purpose, we added a sky pale yellow color with 34% transparency.

Similarly, you can add Borders to a Matrix by toggling the Border option from Off to On.

Format Power BI Matrix Background Color, and Borders

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. 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 the 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.