Power BI Matrix

Power BI Matrix Visual

“Matrix” is a visualization type available with Power Bi. Using this visualization we can create summary reports with simple drag and drop options. This we can call it a “power bi version of pivot tablePower Bi Version Of Pivot TablePivot Table is simply the tool to create a summarized report from the large set of databases. In other words, it facilitates users to create a matrix visualization or summarized report from a large set of databases. However, a user is unable to collapse or expand the items in a Power BI Pivot Table.read more”. This works exactly similar to the pivot table but not exactly a typical pivot table which is with ms excel. This is a built-in visual available in power BI.

One of the common questions of beginners of Power BI is can we use the “Pivot Table” kind of stuff in Power BI to summarize the data? The answer is “kind of pivot table can be used with Power BI to summarize the dataPower BI To Summarize The DataThe SUMMARIZE function summarizes a large number of data rows into one table based on a specified criteria column. For multiple city sales values, for example, if each city has many rows of transactions, the SUMMARIZE function provides a summary table with only one row of transactions for each city.read more”. Not a like for like replacement but at least basic level pivot table summarization is possible with Power BI using “Matrix Visual”.

Power BI Matrix 1

To work with the “Matrix” visualization we need to understand the fields of this visual. Insert a blank “Matrix” visual to see its fields.

Power BI Matrix 1-1

A simple three fields are available with “Matrix” visual. Let’s look at what each field meant to create a summary table.

  1. Rows: While creating a pivot table we need to drag and drop column fields which are to be appeared in rows, so similarly for this field of Matrix we need to drag and drop the column which needs to be summarized with rows.
  2. Columns: Like Rows which column needs to be shown as “Column” will be drag and dropped to this field.
  3. Values: This is nothing but the “value column” which needs to be summarized. For example “Sales Column, Cost Column, Profit Column” etc… based on the requirement.

How to Create Matrix Visual in Power BI? (Examples)

You can download the workbook and use the same data.

You can download this Power BI Matrix Excel Template here – Power BI Matrix Excel Template

To create matrix visual we need some sort of data and we have created dummy data to demonstrate this visual.

Follow the below steps to create a matrix visual in Power BI.

  1. We have already uploaded the data to power bi desktop and it looks like this.

    Matrix Visual in Power BI - Example 1
    Using this data we need to create a summary table.

  2. To insert the blank “Matrix” visual from the visualization list.

    Matrix Visual in Power BI - Example 1-1

  3. For the row field of “Matrix” visual drag and drop “Country” column.

    Matrix Visual in Power BI - Example 1-2

  4. For columns field drag and drop “Product” column.

    Matrix Visual in Power BI - Example 1-3

  5. For the “Values” field drag and drop “Gross Sales” column from the data table.

    Matrix Visual in Power BI - Example 1-4

  6. This will create a “Matrix” visualization in power BI like the below one.

    Matrix Visual in Power BI - Example 1-5

This looks like our pivot table in excel, isn’t it?

Once the visual is created we need to play with its formatting to make it look good, clean and neat.

  • By selecting the visual click on the “Formatting” tab.
Matrix Visual in Power BI - Example 1-6
  • The first thing we need to do is “Style” of the “Matrix” visual. Click on the drop-down list of “Style” formatting option and choose “Bold header”.
Matrix Visual in Power BI - Example 1-7
  • Now matrix table formatted like the below.
Matrix Visual in Power BI - Example 1-8

We can also change the “Column Header” font size, font color, font name, background color, etc. Now, I have set the below formatting options under “Column Header”.

  • Font: Segoe (Bold)
  • Text Size: 12 Pt
  • Alignment: Centre
Matrix Visual in Power BI - Example 1-9
  • And this will format the table like this.
Example 1-10

Similarly for “Row Headers” apply the changes as we did above for “Column Header”.

Example 1-11

I have applied the same changes as I did for “column header” so my table now looks like this.

Example 1-12

Now we need to increase the font size of values. So click on “Values” formatting and apply the below changes.

Example 1-13

I have just increased the font size to 12m, if you wish to have other formatting options you can try all of them.

Now my table looks like this.

Example 1-14

Add Drill Down to Matrix Visual

One of the problems with Matrix visual is it will not work as a pivot table because if you add the “Segment” column to see the break-up sales for each country it won’t give the breakup summary.

Add Drill Down to Matrix Visual 1
  • But one of the flexible things it will give us when we add more than the one-row field is it will enable the option of “Drill Down”.
Add Drill Down to Matrix Visual 1-1
  • Click on the above drill down option to see the break-up.
Add Drill Down 1-2
  • To again go back to the previous option “Drill Up” option
Add Drill Down 1-3

This will bring back the old table summary without any break-up.

Add Drill Down 1-4
  • This will remove row-level subtotals.
Add Drill Down 1-5

Like this using the “Matrix” visual we can create the “Pivot Table” kind of summary table in Power BI.

Note: Power BI dashboard file can also be downloaded from the link below and the final output can be viewed.

You can download this Power BI Matrix Template here – Power BI Matrix Template

Things to Remember

  • Matrix visual works like a pivot table.
  • To view subtotals we need to add columns to respective fields and turn on the “Drill Down” option, you can tune on “Drill Up” to cancel this option.

This has been a guide to Power BI Matrix. Here we learn how to create a matrix visual in Power BI and also add drill-down and drill-up to matrix visual along with examples. You may learn more about Power BI from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion