Power BI Matrix

Last Updated :

-

Blog Author :

Jeevan A Y

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

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. We can call it a “power bi version of pivot table”. It works exactly similar to the pivot table but not exactly a typical PivotTable with MS Excel. Nevertheless, it is a built-in visual available in Power BI.

One common question for beginner Power BI is, can we use the “Pivot Table” stuff in Power BI to summarize the data? The answer is “we can use a PivotTable with Power BI to summarize the data”. Not a like-for-like replacement, but at least basic level PivotTable summarization is possible with Power BI using Matrix visual.

Table of contents
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 the Matrix visual. But, first, let us look at each field's meaning to create a summary table.

  1. Rows: When creating a PivotTable, we must drag and drop column fields appearing 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 dragged 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.

To create a matrix visual, we need some data. Therefore, 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 the Power BI desktop. 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 "Matrix" row field, we must drag and drop the "Country" column.


    Matrix Visual in Power BI - Example 1-2

  4. For the "Columns" field, drag and drop the "Product" column.


    Matrix Visual in Power BI - Example 1-3

  5. For the "Values" field, drag and drop the "Gross Sales" column from the Data Table.


    Matrix Visual in Power BI - Example 1-4

  6. It will create a "Matrix" visualization in Power BI like the below one.


    Matrix Visual in Power BI - Example 1-5

It looks like our PivotTable in Excel.

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 "Format" tab.
Matrix Visual in Power BI - Example 1-6
  • The first thing we need to do is "Style" the "Matrix" visual. Then, click on the dropdown list of the "Style" formatting option, and choose "Bold header."
Matrix Visual in Power BI - Example 1-7
  • Now, the matrix table is 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. We have set the formatting options below under "Column headers."

  • 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 mentioned above for "Column headers."

Example 1-11

We have applied the same changes for "Column headers," so our 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

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

Now, our 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 PivotTable because if you add the "Segment" column to see the break-up sales for each country, it will not give the break-up 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 enabling the "Drill Down" option.
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
  • Again, go back to the last option, the "Drill Up" option.
Add Drill Down 1-3

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

  • In the "Drill Down," if you do not wish to have “Sub Totals” then under the "Format" tab, turn off the "Row Subtotals" options.
Add Drill Down 1-4
  • It will remove row-level subtotals.
Add Drill Down 1-5

Using the "Matrix" visual, we can create the PivotTable summary table in Power BI.

Note: We can also download the Power BI dashboard file from the link below. We can view the final output.

Things to Remember

  • The Matrix visual works like a PivotTable.
  • To view subtotals, we must add columns to respective fields and turn on the "Drill Down" option. We can tune on "Drill Up" to cancel this option.