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”.
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)
You can download the workbook and use the same data.
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.
- 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 TotalsSub TotalsThe Excel Subtotal function is a built-in function that returns the subtotal of a given data table or data sets. This formula requires two inputs, the first of which is the function number and the second of which is the range.read more” 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
50.7K subscribers