WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Power BI Matrix

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

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)

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.

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

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.

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

Matrix Visual in Power BI - Example 1-1

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

Matrix Visual in Power BI - Example 1-2

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

Matrix Visual in Power BI - Example 1-3

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

Matrix Visual in Power BI - Example 1-4

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

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course
  • 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.

  • 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

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

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 –

  • Average Function in Power BI
  • Dashboard in Power BI
  • Power BI Bookmarks
  • Power BI GroupBy
8 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Download Power BI Matrix Template

By continuing above step, you agree to our Terms of Use and Privacy Policy.

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Power BI Matrix Excel Template

New Year Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More