Power BI Matrix - Complete tutorial

In this Power bi tutorial, we will discuss the power bi matrix in the power bi desktop. And also we will discuss the below points:

Power bi matrix
Power bi matrix visual
Power bi matrix vs table
Power bi matrix mesure in row
Power bi matrix multiple column header
Power bi matrix column sort order
Power bi matrix multiple row header
Power bi matrix sort column by value
Power bi matrix formatting
Power bi matrix tabular form
Power bi matrix column width
Power bi matrix add calculated column
Power bi matrix hierarchy
Power bi matrix measures as column

Table of Contents

Introduction to Power bi matrix

The Power bi matrix visual is as similar to table visual.
The power bi matrix visual makes the data easier to display meaningfully across multiple dimension and also it support a stepped layout.
The power bi matrix automatically aggregates the data and enable you to drill down.

Power bi matrix visual

Here we will see how to create a Power bi matrix visual in power bi desktop using sample data.

Open Power bi desktop
Load the data using Get data.
Select the Matrix visualization from power bi report
In the Rows field, drag and drop the Order date(month) from the field pane.
Then in the value field drag and drop the sales and profit from the field pane.

In the below screenshot, you can see the power bi matrix visual in the power bi desktop.

Read Microsoft Power BI Stacked Column Chart

Power bi matrix vs table

Here we will the difference between Power bi matrix vs table.

Matrix	Table
The power bi matrix is multi-dimension like excel pivot table	Whereas power bi table 2-Dimension visual to represent tabular data.
In the power bi matrix, you have the option to add more dimensions to rows, columns,s, and value fields.	In the table, if you want to add more dimension, then you have to add it to the value field, it will appear in the new column
The power bi matrix automatically aggregates the data and enables you to drill down	But there is no easy way to summarize the higher dimension in table visualization and drill down further to the lower dimension.
In power bi matrix rows and column both are not fixed.	In power bi table rows are not fixed and columns are fixed.

Power bi matrix vs table

Read Power Bi Text Functions

Power bi matrix mesure in row

Here we will see the power bi matrix measure in a row.

In power bi desktop, Select the Power bi matrix from the visualization
Then in the column field drag and drop the Order date(month) from the field pane.
Then in the value field add the Total sales and Total profit measure from the field pane.
Total sales =SUM( ‘Order'[Sales]) & Total profit= SUM(‘Order[Profit])

You can see the Power bi matrix puts the measure across the column

Microsoft Power bi matrix measure in row — Power bi matrix measure in row

Now we will put the measure in row in power bi matrix. Go to format section under visualization.
Expand the value section, turn on the Shows on rows.

In the below screenshot you can see power bi matrix measure in row.

Read Power Bi Schedule Refresh

Power bi matrix multiple column header

Here we will see how to add power bi matrix multiple column header in power bi desktop using sample data.

In power bi desktop select the Matrix visualization. Then in the Rows field drag and drop the segment from the field pane.
In the column field drag and drop the order(year) and region from the field pane.
In the value field drag and drop the sales and profit from the field pane.

To show multiple column, we need to expand all down one level in the hierarchy.
So click on the fork icon present on the top of the matrix visual in the canvas.

Go to format section, expand the column header, under allignment select center.

Microsoft Power bi matrix multiple column header — Power bi matrix multiple column header

Now in the below screen shot you can see the power bi matrix multiple column header

Read Power Bi Pyramid chart – How to use

Power bi matrix column sort order

Here we will see the power bi matrix column sort order in power bi desktop.

I will use the below table to create a power bi matrix column sort order.

In Power bi desktop load the data. Click on the Transform data from the ribbon to open the power query editor.
Now go to add column tab, click on the index column select custom.

In the index column dialog box, put starting index = 6 and add increment = -1. Click on Ok.
Click on close and apply from the ribbon

Microsoft Power bi matrix column sort order — Power bi matrix column sort order

Now in power bi desktop go to data tab.
Select the Type cloumn and go to column tools.
Click on Sort by column and select index.

Click on the report tab, select the matrix from the visualization.
In the Row field drag and drop the product from the field pane.
Then in the column field drag and drop the type from the field pane.
In the value field drag and drop the quantity and price from the field pane.

In the below screenshot you can see the Power bi matrix column sort order.

Read Power bi area chart

Power bi matrix multiple row header

Here we will see the Power bi matrix multiple row header in the power bi desktop.

In power bi desktop, select the power bi matrix from the visualization.
In rows field, drag and drop the segment from the field pane
In column field, drag and drop order date(Year) from the field pane.
In the value field, drag and drop the sales and profit from the field pane.

Go to Format tab under visualization, expand the value section.
Then turn on the Show on rows.

Now in the below screenshot, you can see the Power bi matrix multiple row header in the power bi desktop.

Read Power Bi line chart

Power bi matrix sort column by value

Here we will see the power bi matrix sort column by value in the power bi desktop.

In the below screenshot, you can see the sample data in the power bi desktop.

In Power bi desktop, select matrix from the visualization.
In the row field add item type, and add country to the column field.
In the value field, add price from the field pane.

In the below screenshot, you can see the power bi matrix visual.

Now click on the Transform data from the ribbon, to open power query editor.
Right click on the original table, and then select the Duplicate.

Select the country column, and right click on it. Select Remove other column.

Now, you have only country column left in the duplicate table. Right click on the country column header and select Remove duplicates.
Click on close and apply from the ribbon in the power query.

Now power bi automatically create the relationship between the original table and duplicate table
Next we will create a measure, Click on the New measure from the ribbon.
And the measure is:

Sales = 

VAR Sales = SUM('Car Sales'[Price])
VAR SortContext = DISTINCTCOUNT('Country'[Country])>1
VAR IsTotal = IF(SELECTEDVALUE(Country[Country]) = "Total", TRUE(), FALSE())


RETURN

SWITCH(TRUE(), SortContext, CALCULATE(SUM('Car Sales'[Price]), USERELATIONSHIP('Car Sales'[Country], 'Country (Sort)'[Lookup])), CALCULATE(SUM('Car Sales'[Price]),USERELATIONSHIP('Car Sales'[Country], 'Country'[Lookup])))

Now select a slicer from the visualization and add country from the field pane to the Field.

From the slicer you can select the country. And click on the more icon on the top of the visual.
Click on the sort by select Item type.

Microsoft Power bi matrix sort column by value

In the below screen-shot you can see the Power bi matrix sort column by value

Read Power Bi Bar Chart

Power bi matrix formatting

Here we will see power bi matrix formatting in the power bi desktop.

The Power bi Matrix formatting includes Matrix grid colors, Column Formatting, Row formatting, row, and column colors, Matrix title colors, and many more.

In power bi desktop select matrix from the visualization pane. In the row field, drag and drop the segment from the field pane.
In the column field, drag and drop the Order date(year)from the field pane.
In the value field, drag and drop the Profit, sales and COGS from the field pane.

In the visualization pane go to format section.
Here you can see different types power bi matrix formatting.

Power bi matrix style

In the power bi matrix style you can see the list of styles. Here you can select the style as per your requirement.

power bi matrix format style — Microsoft Power bi matrix formatting

If i select the flashy rows style for our power bi matrix, you can see the power bi matrix formatting.

Grid

In the grid section there is list of options are available for you to format in power bi matrix.
Vertical grid: Toggling this feature from off to on adding vertical grid to the matrix visual. And aso you can add vertical grid thickness and color.
Horizontal grid: Toggling this feature from off to on adding horizontal grid to the matrix visual. And aso you can add horizontal grid thickness and color.
Row padding: Toggling this feature fron turn off to on will add padding to rows.
And you can also add outline weight, outline color, and text size.

In the below screenshot, you can see the power bi matrix formatting grid horizontally.

Column Header

In the column header section, we can change the font color, background color, outline, font family, text size, Allignment, and title allignment.
There is option to change the width of the column automatically by toggling on the option.

In the below screenshot you can see power bi matrix formatting, change the background color of the column header to blue.

Row header

Similarly in the row header, we can change the font color, background color, outline, font family, text size, Allignment, and title allignment.

Values

In this section, you can format the font color, alternative rows font color, background color, alternative background color.
And also you can toggle on banded row style, and shows on rows.

In the below screenshot you can see the power bi matrix formatting.

Sub-Total

In the sub total section, you can alter the text and background color of subtotals.
By toggling off Row subtotal and column subtotal, you can remove the totals from the power bi matrix.

In the below screenshot, you can see the power bi matrix formatting shows column subtotal.

Grand total

In the grand total section, you can alter the text and background color of total.
And also you can change the font color, background color, font family and text size.
By toggling ‘apply to label’ on, it will apply background color to the header of the total.

In the below screenshot you can see the power bi matrix foramting based on grand total.

Field Formatting

In this section, you can format the individual column in the power bi matrix.
According to each column, you can change the display units, value decimal places, font color, background color, allignment
By toggling on apply to header, apply to values, apply to subtotal and apply to total, will apply background color on header, values, subtotal and total respectively.

In the below screenshot, you can see the power bi matrix field formatting.

Conditional formatting

In this section you can add the conditional formatting based the specific field.
By toggling on the background color, yoyu can see the background color of the selected field. And also you can add custom conditional formatting for background color by clicking on Advanced control.
And also you can add font colors and data bars, and also you can add the custom conditional formatting by clicking on Advanced control.
By toggling on icon section, you can add icon and also you add custom icon by clicking on Advanced control.

In the below screenshot, you can see power bi matrix conditional formatting in the power bi desktop.

Read Power BI Pie Chart – Complete Tutorial

Power bi matrix tabular form

Here we will see the Power bi matrix tabular form in the power bi desktop.

In power bi desktop select the matrix from the visualization pane.
Then drag and drop the region and segment from the field pane to the Row field
Then drag and drop the profit column from the field pane to the field value.

power bi matrix table format — Power bi matrix tabular form

Now expand the each row in the visualization by clicking on the plus icon..

Click on the Format section under visualization.
Expand the row header section and turn off the stepped layout functionality.

Then Expand the subtotal column and turn on the Row subtotal and per row level.

microsoft Power bi matrix tabular form — Microsoft Power bi matrix tabular form

In the below screenshot you can see the power bi matrix tabular form.

Read How to use Power bi ribbon chart

Power bi matrix column width

Here we will see Power bi matrix column width in the power bi desktop using the sample data.

The “auto-size column width” feature in the power bi desktop, automatically resizes the width of the power bi matrix column depending on the size of the value.

Now we will see the steps in resizing the matrix column to have the same size across the table.

In power bi desktop select the matrix from the visualization pane.
In the row field, drag and drop the segment from the field pane.
In the column field, drag and drop the region from the field pane.
In the value field, drag and drop the profit and sales from the field pane.

In the below screenshot you can see the power bi matrix before resizing.

before resizing the matrix column — Power bi matrix column width

Now we will add a measure to resize the column. The measure is ResizeColWidth = REPT(“0”,15)

Power bi matrix column width

Go to format section under the visualization pane, Expand the value section and turn on the Show on rows option.

measure to resize the column width 1 — Power bi matrix column width

Then expand the column header, and turn on the Auto size column width.

Auto size column width — Power bi matrix column width

Now drag and drop the measure we have created, to value field. It will resize the column of the matrix visual.
In the below screenshot you can see the matrix visual resize all column. According to your requirement you can specify the width while creating measure.

Save the report in power bi desktop and turn off the ‘Auto size column width’ from column header in Formatting section.
After turning off the ‘Auto size column width’, save again.
Then Remove the measure from the visualization, size of the column in matrix column persist even if the new column will be addded.
In the below screenshot you can see the Power bi matrix column width.

Read Power BI Filter vs Slicer

Power bi matrix add calculated column

Here we will see the power bi matrix add a calculated column using sample data in the power bi desktop

In power bi desktop, go to data tab.
Click on the new column, to create a calculated column.

Power bi matrix add calculated column — Power bi matrix add a calculated column

This calculate column will show the COGS value, for that we will write the Dax formula.
The Formula is COGS = Orders[Sales]-Orders[Profit].

Now we will use this calculated column in power bi matrix.
For that select the power bi matrix from the visualization pane in power bi desktop.
In the row field, drag and drop the segment from the field pane.
In the column field, drag and drop the category from the field pane.
In the value field, drag and drop the profit sales and COGS(calculated column)from the field pane.

In the below screenshot, you can see the Power bi matrix add a calculated column.

Power bi matrix hierarchy

Here we will see power bi matrix hierarchy in the power bi desktop using sample data.

In power bi desktop select the matrix from the visualization pane
In the row field, add city, country, category, region and segment from the field pane.

In the canvas, click on the fork icon to expand one level of hierarchy.
According to your requirement, expand the level down in hierarchy. I have expanded 4 level down in hierarchy.

In the below screenshot, you can see the Power bi matrix hierarchy in the power bi desktop.

Read How to use Power bi maps

Power bi matrix measures as column

Here we will see the power bi matrix measure as a column in the power bi desktop.

In power bi desktop select rows from the visualization pane.
In the row field, drag and drop the Order date(year) and Region from the field pane.
In the value field, drag and drop the Total sales and Total profit (measure)from the field pane.
The dax formula of total sales is Total sales = SUM(Orders[Sales])
The dax formula of total profit is Total profit = SUM(Orders[Profit])

In the below screenshot, you can see the Power bi matrix measures as a column.

You may like the following Power BI tutorials:

In this power bi tutorial, we learn about the power bi matrix. And also we discuss the below points:

Power bi matrix
Power bi matrix visual
Power bi matrix vs table
Power bi matrix mesure in row
Power bi matrix multiple column header
Power bi matrix column sort order
Power bi matrix multiple row header
Power bi matrix sort column by value
Power bi matrix formatting
Power bi matrix tabular form
Power bi matrix column width
Power bi matrix add calculated column
Power bi matrix hierarchy
Power bi matrix measures as column

Bhawana Rathore

Bhawana Rathore is a Microsoft MVP (3 times in Office Apps & Services) and a passionate SharePoint Consultant, having around 10 years of IT experience in the industry, as well as in .Net technologies. She likes to share her technical expertise in EnjoySharePoint.com and SPGuides.com