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

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.
Power bi matrix visual
Power bi matrix visual

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

Power bi matrix visual
Power bi matrix visual

Read Microsoft Power BI Stacked Column Chart

Power bi matrix vs table

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

MatrixTable
The power bi matrix is multi-dimension like excel pivot tableWhereas 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 downBut 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

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])
Power bi matrix measure in row
Power bi matrix measure in row
  • 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.
Power bi matrix measure in row
Power bi matrix measure in row
  • In the below screenshot you can see power bi matrix measure in row.
Power bi matrix measure in row
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.
Power bi matrix multiple column header
Power bi matrix multiple column headers
  • 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.
Power bi matrix multiple column header
Power bi matrix multiple column headers
  • 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
Microsoft Power bi matrix multiple column header
Microsoft Power bi matrix multiple column headers

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.

Power bi matrix column sort order
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.
Power bi matrix column sort order
Power bi matrix column sort order
  • 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.
Power bi matrix column sort order
Power bi matrix column sort order
  • 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.
Power bi matrix column sort order
Power bi matrix column sort order

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

Power bi matrix column sort order
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.
Power bi matrix multiple row header
Power bi matrix multiple row header
  • Go to Format tab under visualization, expand the value section.
  • Then turn on the Show on rows.
Power bi matrix multiple row header
Power bi matrix multiple row header

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

Power bi matrix multiple row header
Power bi matrix multiple row header

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.

Power bi matrix sort column by value
Power bi matrix sort column by value
  • 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.
Power bi matrix sort column by value
Power bi matrix sort column by value

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

Power bi matrix sort column by value
Power bi matrix sort column by value
  • 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.
Power bi matrix sort column by value
Power bi matrix sort column by value
  • Select the country column, and right click on it. Select Remove other column.
Power bi matrix sort column by value
Power bi matrix sort column by value
  • 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.
Power bi matrix sort column by value
Power bi matrix sort column by value
  • 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.
Power bi matrix sort column by value
Power bi matrix sort column by value
  • 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
Microsoft Power bi matrix sort column by value

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

Microsoft Power bi matrix sort column by value
Microsoft 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.
Power bi matrix formatting
Power bi matrix formatting
  • In the visualization pane go to format section.
  • Here you can see different types power bi matrix formatting.
Microsoft Power bi matrix formatting
Microsoft 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.
Microsoft Power bi matrix formatting
Microsoft 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.
Microsoft Power bi matrix formatting
Microsoft Power bi matrix formatting

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

Microsoft Power bi matrix formatting
Microsoft Power bi matrix formatting

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.
Microsoft Power bi matrix formatting
Microsoft Power bi matrix formatting

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

Microsoft Power bi matrix formatting
Microsoft Power bi matrix formatting

Row header

  • Similarly in the row header, we can change the font color, background color, outline, font family, text size, Allignment, and title allignment.
Microsoft Power bi matrix formatting
Microsoft Power bi matrix formatting

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.
Microsoft Power bi matrix formatting
Microsoft Power bi matrix formatting

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

Microsoft Power bi matrix formatting
Microsoft 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.
Microsoft Power bi matrix formatting
Microsoft Power bi matrix formatting

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

Microsoft Power bi matrix formatting
Microsoft Power bi matrix formatting

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.
Microsoft Power bi matrix formatting
Microsoft Power bi matrix formatting
  • In the below screenshot you can see the power bi matrix foramting based on grand total.
Microsoft Power bi matrix formatting
Microsoft Power bi matrix formatting

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.
Microsoft Power bi matrix formatting
Microsoft Power bi matrix formatting

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

Microsoft Power bi matrix formatting
Microsoft Power bi matrix 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.
Microsoft Power bi matrix formatting
Microsoft Power bi matrix formatting

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

Microsoft Power bi matrix formatting
Microsoft Power bi matrix formatting

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..
Power bi matrix tabular form
Power bi matrix tabular form
  • Click on the Format section under visualization.
  • Expand the row header section and turn off the stepped layout functionality.
Power bi matrix tabular form
Power bi matrix tabular form
  • 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.

microsoft Power bi matrix tabular form
Microsoft 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.
Power bi matrix column width
Power bi matrix column width

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
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.
Power bi matrix column width
Power bi matrix column width
  • 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.
Power bi matrix column width
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].
Power bi matrix add calculated column
Power bi matrix add a calculated column
  • 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.
Power bi matrix add calculated column
Power bi matrix add a calculated column

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

Power bi matrix add calculated column
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.
Power bi matrix hierarchy
Power bi matrix hierarchy
  • 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.
Power bi matrix hierarchy
Power bi matrix hierarchy

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

Power bi matrix hierarchy
Power bi matrix hierarchy

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])
Power bi matrix measures as column
Power bi matrix measures as column

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

Power bi matrix measures as column
Power bi matrix measures as 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
>