Power BI Matrix

Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

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 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”. It works exactly similar to the pivot tablePivot TableA Pivot Table is an Excel tool that allows you to extract data in a preferred format (dashboard/reports) from large data sets contained within a worksheet. It can summarize, sort, group, and reorganize data, as well as execute other complex calculations on it.read more 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 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 PivotTable 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 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.

–>> If you want to learn Power BI professionally, then Power BI Basics Course (5+ hours) is the perfect solution. This course will introduce you to a wide range of Power BI concepts like collection of software services, apps, and connectors that work together to turn your unrelated sources of data into coherent, visually immersive, and interactive insights, Creating Reports in Power BI Desktop, Analyze Data with Excel, Graphs & Visualizations, Interactive Dashboards along with some real life examples.

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

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.

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

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.

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