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 table”. 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 data”. Not a like for like replacement but at least basic level pivot table summarization is possible with Power BI using “Matrix Visual”.
To work with the “Matrix” visualization we need to understand the fields of this visual. Insert a blank “Matrix” visual to see its fields.
A simple three fields are available with “Matrix” visual. Let’s look at what each field meant to create a summary table.
- 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.
- Columns: Like Rows which column needs to be shown as “Column” will be drag and dropped to this field.
- 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)
To create matrix visual we need some sort of data and we have created dummy data to demonstrate this visual. You can download the workbook and use the same data.
We have already uploaded the data to power bi desktop and it looks like this.
Using this data we need to create a summary table.
- To insert the blank “Matrix” visual from the visualization list.
- For the row field of “Matrix” visual drag and drop “Country” column.
- For columns field drag and drop “Product” column.
- For the “Values” field drag and drop “Gross Sales” column from the data table.
- This will create a “Matrix” visualization in power BI like the below one.
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.
- 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”.
- Now matrix table formatted like the below.
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
- And this will format the table like this.
Similarly for “Row Headers” apply the changes as we did above for “Column Header”.
I have applied the same changes as I did for “column header” so my table now looks like this.
Now we need to increase the font size of values. So click on “Values” formatting and apply the below changes.
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.
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.
- 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”.
- Click on the above drill down option to see the break-up.
- To again go back to the previous option “Drill Up” option
This 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.
- This will remove row-level subtotals.
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.
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.
Recommended Articles
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
