Power BI Matrix Multiple Column

Are you looking forward to learning and knowing about the Power BI Matrix Multiple columns? Do not hesitate. Here is the complete tutorial where you can find how to work with Power BI Matrix visualization, and what is the difference between Matrix visual and Table visual.

Also, this tutorial covers the below-mentioned topics:

  • What is the Power BI matrix
  • How to use the power bi matrix visual
  • How to add two columns in the Power bi matrix
  • How to order columns in Power Bi matrix
  • Power Bi matrix displays multiple columns
  • Power Bi matrix multiple columns headers
  • Power Bi matrix multiple row headers
  • Power Bi matrix conditional formatting multiple columns
  • Power Bi matrix two fields in a row
  • Power Bi matrix divides two columns
  • How to group columns in the Power BI matrix
  • Power BI matrix various columns in a row
  • How to hide a column Power Bi matrix
  • How to move a column Power Bi matrix
  • Power Bi matrix multiple columns sort
  • Power Bi matrix compares two columns
  • Power Bi matrix expands multiple columns
  • Power Bi matrix multiple total columns

Power BI matrix

  • The matrix visual in Power bi is similar to the table visual in Power BI.
  • The power bi matrix is multiple dimensions and rows and columns are not fixed.
  • We have the option to add more dimensions to rows, columns, and value fields in the Power Bi matrix visual.

Also, read: Power BI Add Calculated Column [With Various Examples]

Power Bi Matrix visual vs Table visual

The below table represents the difference between Matrix visual and Table visual in Power BI,

Matrix visual Power Bi Table visual Power Bi
Matrix visual is to represent the data across multiple dimensions. Matrix visual is to represent the data with 2 dimensions.
In Matrix visual, we have an option to add rows, columns, and values. Individual dimensions in the data can be added as a row in a Matrix visual Power Bi. If we want to add more than two dimensions, then we can add them as ‘values’, which appear as new columns in Table visual Power Bi.
Using the drill-down buttons features we can go to the previous level or next level in the matrix visual Power BI. The drill-down feature is not presented in the Table visual Power BI.
Power Bi Matrix visual vs Table visual

Also, read: How to remove rows in power query editor [With various examples]

How to use the Power Bi matrix visual

Let us see how to create and use the Matrix visual to represent the data in Power Bi,

In this complete post, I have used the below-mentioned Products Table data example, and you can download the table data from here.

Power Bi matrix adds multiple columns
Power Bi matrix adds multiple columns
  • Initially, Open the Power BI desktop and load the data using the Get data option.
  • Select the Matrix visualization from the visualization to the Power BI report.
  • In the Rows field, drag and drop the Product column field, and in the value section drag and drop the Sold Amount and Sold Qty from the field pane as shown below:
How to use the Power Bi matrix visual
How to use the Power Bi matrix visual
  • The below screenshot represents the data in the Matrix visualization in Power BI.
How to use the Power Bi matrix visual example
How to use the Power Bi matrix visual example

This is how to create and use the Matrix visual to represent the data in Power BI.

Check out: Power BI filter between two dates [With 15+ Examples]

How to add two columns in the Power bi matrix

Let us see how we can add the two or more columns in the Power BI matrix visual,

In this example also I have used the same Products Table data.

  • Initially, Open Power bi desktop and load the data using the Get data option.
  • Select the Matrix visualization from the visualization to the Power BI report.
  • In the Rows field, drag and drop the Product column field, and in the value section drag and drop the Sold Amount and Sold Qty from the field pane.
  • To add more than two columns in the matrix visual, we can directly add them under the values section.
Power Bi matrix adds multiple columns example
Power Bi matrix adds multiple columns example
  • In the below screenshot, you can see under the values section I have added one more column field called Accessory.
How to add two columns in the Power bi matrix
How to add two columns in the Power bi matrix

This is how to add two or more columns in the matrix visual Power BI.

Read: Power BI Sum Multiple columns

How to Sort order columns in Power Bi matrix

Let us see how we can sort the order column on the Power BI desktop.

  • In this example also, I have used the Same Products table data. Initially, open the Power Bi report to sort order columns x-axis.
  • Now select the Sort axis and choose the column data field. And also you can see we can sort the columns either in the Sort ascending manner or Sort descending manner.
How to Sort order columns in Power Bi matrix
How to Sort order columns in Power Bi matrix
  • The below-represented screenshot sorted the x-axis field data in the ascending form (ie, A to Z form).
How to Sort order columns in Power Bi matrix example
How to Sort order columns in Power Bi matrix example
  • The below-represented screenshot, sorted the x-axis field data in the descending form (ie, Z to A form).
Sort order columns in Power Bi matrix example
Sort order columns in the Power Bi matrix example

This is how we can sort the order columns on the Power BI desktop.

Also, read: Power BI divides two columns [With Examples]

Power Bi matrix display multiple columns

Let us see how we can display the matrix visual with Multiple columns in Power BI,

  • Once the data has been loaded to the Power BI desktop, select the matrix visual from the visualizations.
  • In the Rows field, drag and drop the Accessory column field, and in the value section drag and drop the Sold Amount and Sold Qty from the field pane.
  • And In the column section, drag and drop columns that you want to display, In this example, I have selected Accessory and Product columns fields as shown below:
Power Bi matrix display multiple columns
Power Bi matrix display multiple columns
  • Using the drill-down features to display the multiple columns in the Matrix visual Power BI as mentioned below: This is the first level hierarchy.
Power Bi matrix display multiple columns example
Power Bi matrix display multiple columns example

This is how we can display the matrix visual with Multiple columns in Power BI.

Check out: Power BI Report Export to PDF [With 20+ Examples]

Power Bi matrix multiple columns headers

Let us see how we can edit and format the matrix multiple column headers in Power BI,

  • Select the Matrix visual with Multiple column headers, In the format visual section, under the Visual settings expand the column headers.
  • The below-mentioned options are available under the column header options: where we can change the font family, font size, text color, background color, header alignment Title alignment, etc.,
Power Bi matrix multiple columns headers
Power Bi matrix multiple columns headers
  • In the below screenshot, you can see that the Column Header is aligned with the center.
  • We also have an option to reset to the default values, by selecting the reset to default option.
Power Bi matrix multiple columns headers example
Power Bi matrix multiple columns headers example

This is how we can edit and format the matrix multiple-column headers in Power BI.

Check: Stacked Bar Chart in Power BI [With 27 Real Examples]

Power BI matrix multiple row headers

Let us see how we can edit and format the matrix multiple row headers in Power BI,

  • Select the Matrix visual with Multiple row headers, In the format visual section, under the Visual settings expand the Row headers.
  • Under the Row headers option, we can see three more options presented which are Text, Icons, and options.
  • The below-mentioned options are available under the row header text options: where we can change the font family, font size, text color, background color, header alignment Title alignment, etc.,
  • Under the Icons option, we can choose the color and size of the icons presented in the matrix visual.
Power BI matrix multiple row headers
Power BI matrix multiple row headers
  • In the below screenshot, you can see that the row Header is aligned with the center, and also I have changed the color and size for the selected matrix visual under the icons option.
  • We also have an option to reset to the default values, by selecting the reset to default option.
Power BI matrix multiple row headers example
Power BI matrix multiple row headers examples

This is how we can edit and format the matrix multiple-row headers in Power BI.

Read: Power BI Bookmarks [With 21 Examples]

Power Bi matrix conditional formatting multiple columns

Let us see how we can apply conditional formatting based on the value for the selected Matrix visual chart in the Power BI,

  • In this example, we will format the background of the cell element based on the condition, If the Sold Amount value is greater than 2000 and less than 5000 then the cell element is Yellow in color.
  • If the sales value is greater than 5100 and less than 30000 then the cell element displays blue color.

Follow the below-mentioned steps to apply conditional formatting in the Matrix visual Power BI:

  • Select the Matrix visualization from the visualization to the Power BI report.
  • In the Rows field, drag and drop the Product column field, and in the value section drag and drop the Sold Amount and Sold Qty from the field pane as shown below:
Power Bi matrix conditional formatting for multiple columns
Power Bi matrix conditional formatting for multiple columns
  • Select the Matrix visual for which you want to format the cell elements, and choose the Format Visual under the Visualisation pane.
  • In the Visual settings, expand the cell elements and enable the background color, and click on the Fx icon as shown below:
Power Bi matrix conditional formatting multiple columns
Power Bi matrix conditional formatting multiple columns
  • Now, Select the Format style as Rules and choose the column field value, Now add the rule that you want to apply. Select +New rule to add a new rule condition.
  • Here, If the Sold Amount value is greater than 2000 and less than 5000 then the cell element displays the data in Yellow color.
  • If the sales value is greater than 5100 and less than 30000 then the cell elements display the data in blue color.
  • Click on the Ok button so the matrix visual will appear based on the condition applied.
Power Bi matrix conditional formatting multiple columns example
Power Bi matrix conditional formatting multiple columns examples
  • In the below screenshot, we can see that the matrix cell element color changes based on the Values or conditions applied to the Sold Amount column.
matrix conditional formatting in Power Bi
matrix conditional formatting in Power Bi
  • In the same way, we can apply or format the matrix cell elements or the font color based on the condition for the selected series.
matrix conditional formatting in Power Bi example
matrix conditional formatting in Power Bi example
  • In the below screenshot, you can see that the conditional formatting applied for the sold qty column or series.
matrix conditional formatting in the Power Bi
matrix conditional formatting in the Power Bi

This is how to apply conditional formatting based on the value for the selected Matrix visual chart in Power BI.

Check: Clustered Column Chart in Power BI [With 45 Real Examples]

Power Bi matrix two fields in a row

Let us see how we can add the matrix visual with the two column fields in a row at the same level in Power BI,

Yes, it is possible to display the Power BI matrix visual with the two column fields in a row with the same level.

  • Select the Matrix visualization from the visualization to the Power BI report.
  • In the Rows field, drag and drop the Product and Accessory column fields, and in the value section drag and drop the Sold Amount and Sold Qty from the field pane as shown below:
Power Bi matrix two fields in a row
Power Bi matrix two fields in a row
  • To show the row information at the same level, we should turn off the Stepped Layout. By default, the Stepped Layout option will be enabled.
  • To display it , go to the Formal Pane -> Visual settings -> Row headers -> options. Expand the options and disable the Stepped layout.
  • Once the Stepped layout is disabled, we can see that the row information displays the product column field and accessory column field at the same level as shown below:
  • We also have an option to reset to the default values, by selecting the reset to default option.
Show two rows on same level
Show two rows on the same level

This is how to add the matrix visual with the two column fields in a row at the same level in Power BI.

Read: Power BI Compares Two Columns in Different Tables

Power Bi matrix divides two columns

Let us see how to divide two columns and display the data in the Power BI matrix visual using DAX,

In this example, we will divide and calculate the Products based on the Sold Amount and Sold quantity,

  • Select the Matrix visualization from the visualization to the Power BI report.
  • In the Rows field, drag and drop the Product column field, and in the value section drag and drop the Sold Amount and Sold Qty from the field pane as shown below:
Power Bi matrix divides two columns
Power Bi matrix divides two columns
  • Now create a new measure and apply the below-mentioned formula to calculate the Products based on the Sold Amount and Sold Qty.
Products = SUM(Products[Sold Amount])/SUM(Products[Sold Qty])

Where,

  1. Products = Measures Name
  2. Products = Table Name
  3. Sold AmountSold Qty = Column Names
  • Now drag and drop the created measure in the Values field as mentioned below which displays the result of the division of two columns in the matrix visual.
Power Bi matrix divides two columns example
Power Bi matrix divides two columns example

This is how to divide two columns and display the data in the Power BI matrix visual using DAX.

Also, check: Power bi change color based on value [With 13 real examples]

How to group columns in the Power BI matrix

Let us see how we can group columns and show the grouped data in the Power BI matrix,

  • In this example, I have used the below-mentioned sample vehicle data, so that based on the vehicle type I can group and show the data.
How to group columns in the Power BI matrix
How to group columns in the Power BI matrix
  • Select the Matrix visualization from the visualization to the Power BI report.
  • In the Rows field, drag and drop the Parent and Child column field, and in the value section drag and drop the Sales Count from the field pane as shown below:
  • Then select the drill down icon, so that it will group the columns and displays the data in the matrix visual.
group columns in the Power BI matrix
group columns in the Power BI matrix
  • Now in the below screenshot, you can see that it groups the column data based on the vehicle type.
group columns in the Power BI matrix example
group columns in the Power BI matrix example

This is how to group columns and show the grouped data in the Power BI matrix.

Power BI matrix various columns in a row

Let us see how we can display the multiple row values on the matrix visualization in Power BI,

  • Before we can see the Power BI report displays the various column in a row without using the drill down option.
  • Now after the updated version of the Power BI, it displays the various column in a row only after selecting the drill down option. In this example also, I am going to use the same vehicle data which I used for the previous heading.
  • Select the Matrix visualization from the visualization to the Power BI report.
  • In the Rows field, drag and drop the two or more column fields, here I have selected the Parent and Child column field as shown below:
Power BI matrix various columns in a row
Power BI matrix various columns in a row
  • And then select the drill down presents in the matrix visual as mentioned below: If the stepped layout option is enabled in the Row header then we can see the result as below:
Power BI matrix various columns in a row example
Power BI matrix various columns in a row example
  • In the formatting pane, under the row header-> options if the stepped layout is disabled, we can see the results as below:
Power BI matrix various columns in the row
Power BI matrix various columns in the row

This is how to display the multiple row values on the matrix visualization in Power BI.

Read: Power BI calculated column [With 71 Useful Examples]

How to hide a column Power Bi matrix

Let us see how we can hide a column in the Power Bi matrix visualization,

  • Select the matrix visualization, for which you want to hide a column. In this example, I am going to hide the Sales count column as highlighted in the below screenshot:
How to hide a column Power Bi matrix
How to hide a column Power Bi matrix
  • We just need to reduce its column size to zero, by resizing the column width.
hide a column Power Bi matrix
hide a column Power Bi matrix
  • To hide this empty white space, make sure to disable the word wrap option on the column header under the formatting pane.
  • In the below screenshot, you can see that the column is hidden in the Power BI matrix visualization.
hide a column Power Bi matrix example
hide a column Power Bi matrix example

This is how to hide a column in the Power Bi matrix visualization.

How to move a column Power Bi matrix

Let us see how we can move or exchange a column in the Power Bi matrix visualization,

Yes, it is possible to move a column in the Power BI matrix visualization, by reordering the columns in the column field section.

  • We can directly move or exchange the columns presented in the visual, if we want to move or exchange then only we can change in the column field section.
  • Select the matrix visualization, for which you want to move a column. In this example, under the column section, I have added the Parent and Child column fields.
How to move a column Power Bi matrix
How to move a column Power Bi matrix
  • Now after the updated version of the Power BI, it displays the various column in a column section only after selecting the drill down option.
How to move a column Power Bi matrix example
How to move a column Power Bi matrix example

This is how to move or exchange a column in the Power Bi matrix visualization.

Power Bi matrix multiple columns sort

Let us see how we can sort the matrix multiple columns with example in the Power BI desktop,

In this example also, I have used the Same Products table data.

  • Initially, Select the Matrix visual. In the row field drag and drop the Product field, and add the Accessory to the column field.
  • In the value field, add Sold Amount field from the field pane as mentioned below.
Power Bi matrix multiple columns sort
Power Bi matrix multiple columns sort
  • Now select the matrix visual, click on the ellipsis icon and choose the sort by option.
  • Choose the column that you want to sort accordingly. Here I am going to sort the column based on the Product column field.
Power Bi matrix multiple columns sort example
Power Bi matrix multiple columns sort example
  • The below-represented screenshot sorted based on the Sold Amount column field in matrix visual.
Power BI matrix multiple column sort example
Power BI matrix multiple column sort example

This is how to sort the matrix multiple columns in the Power BI desktop.

Read Power Query Add Column

Power Bi matrix expands multiple columns

Let us see how we can expand or collapse the multiple columns in the Power BI matrix visual,

  • In this example also, I have used the Same Products table data.
  • In the same way, Select the Matrix visual. In the row field drag and drop the Product and Accessory column fields.
  • In the value field, drag and drop the Sold Amount and Sold Qty from the field pane as mentioned below:
Power Bi matrix expands multiple columns
Power Bi matrix expands multiple columns
  • Yes, It is possible to expand or collapse the values in the matrix visual Power BI.
  • And the below screenshot represents the Collapse values in the Power BI matrix visual. To expand the matrix values, click on the + icon as highlighted below:
Power Bi matrix collapse multiple columns
Power Bi matrix collapse multiple columns
  • The below screenshot represents the Expand values in the Power BI matrix visual. To collapse the matrix values, click on the – icon as highlighted below:
Power Bi matrix expands multiple columns example
Power Bi matrix expands multiple columns example

This is how we can expand or collapse the multiple columns in the Power BI matrix visual.

Power Bi matrix compares two columns

Let us see how we can compare two columns and display the result in the matrix visual in Power BI.

Here, In this example, I have used the Product table data, where I going to calculate the remaining qty value by comparing the total qty and sold qty.

Power Bi matrix compares two columns
Power Bi matrix compares two columns
  • Select the Matrix visual. In the row field drag and drop the Product column. And In the value field, drag and drop the Total Qty and Sold Qty from the field pane as shown below:
Power Bi matrix compares two columns example
Power Bi matrix compares two columns example
  • Now create a new measure to calculate the remaining qty by comparing the two column fields by applying the below-mentioned formula:
Measure = SUM(Products[Total Qty])-SUM(Products[Sold Qty])
  • And then later drag and drop the newly created measure in the values field.
  • In the below screenshot, you can see the remaining qty value by comparing the total qty and sold qty.
Power Bi matrix difference two columns
Power Bi matrix difference two columns

This is how to compare two columns and display the result in the matrix visual in Power BI.

Power Bi matrix multiple total columns

Let us see how we can calculate the multiple total columns in the Power BI matrix visual,

Here, In this example, I have used the Product table data to calculate the matrix multiple total columns,

  • Select the matrix visual. In the row field drag and drop the Product and Accessory column fields. In the value field drag and drop the Total Qty and Sold Qty from the field pane as shown below:
Power Bi matrix multiple total columns
Power Bi matrix multiple total columns
  • By default, the matrix visual calculates and groups the multiple columns total from the data set and displays the results in the matrix visual Power BI as displayed below:
  • And it is possible to change the total value, such as Average value, Count, etc., by expanding the value field.
Power Bi matrix multiple total columns example
Power Bi matrix multiple total columns example

This is how to calculate the multiple total columns in the Power BI matrix visual.

Also, you may like the below Power BI Tutorials:

In this Power BI tutorial, we learned the Power Bi matrix multiple columns, And also discussed the below points:

  • Power Bi Matrix visual vs Table visual
  •  How to use the power bi matrix visual
  • How to add two columns in the Power bi matrix
  • How to order columns in Power Bi matrix
  • Power Bi matrix displays multiple columns
  • Power Bi matrix multiple columns headers
  • Power Bi matrix multiple row headers
  • Power Bi matrix conditional formatting multiple columns
  • Power Bi matrix two fields in a row
  • Power Bi matrix divides two columns
  • How to group columns in the Power BI matrix
  • Power BI matrix various columns in a row
  • How to hide a column Power Bi matrix
  • How to move a column Power Bi matrix
  • Power Bi matrix multiple columns sort
  • Power Bi matrix multiple total columns
  • Power Bi matrix compares two columns
  • Power Bi matrix expands multiple columns
>