One of the most curious things for the users who started learning Power BI is to know how to create pivot tables in Power BI. Well, in Power BI, we have a visualization called Matrix. In this tutorial, we are going to learn the Power BI Matrix in detail.
What is a Matrix in Power BI?
In Power BI, a matrix is a visualization that looks like a summary table of large data, combines the functionalities of tables and pivot tables. This helps you to present data in a compact by summarizing data in a grid form where you have rows, columns, and values. One of the primary benefits of using a Matrix in Power BI is its ability to handle multidimensional data.
In this guide, once you learn how to create a Matrix, we will learn all its benefits in detail.
Steps for Creating a Matrix in Power BI
You can create a matrix in Power BI by following the following steps.
- Insert Matrix Visual: Click the Matrix visual icon in the Visualizations pane. This will add a blank Matrix visual to your report canvas, ready for you to add data.
- Select the Data: Next, you need to define what data you want to display in the Matrix. You will find all the available fields from your dataset in the Fields pane. Here, you can drag and drop the fields you want to use as rows and columns.
- For Rows add the “division” which is the division of the state in the US.
- For Columns add the “yea” to get multiple columns based on the years.
- For Values add amount.
- Default Summary: By default, the values in the Matrix will be summarized according to the selected field’s default summarization. Depending on the field’s nature, this could be a count (number of items), sum (total), average, etc. For example, in our example, we have the amount, and that’s why we have a sum of the amount values.
- Customize the Matrix: The Matrix visual can be further customized using formatting. You can adjust the style, color, text size, and other settings to suit your needs.
Drill Down Option
Power BI Matrix supports hierarchical data, meaning you can have multiple levels in rows and columns. Add various fields to the Rows and Columns to display the data hierarchically. It will automatically create a hierarchy, which you can expand and collapse within the Matrix visual for detailed analysis or a high-level overview.
In the example below, we used two hierarchies in the rows section; one is division, which we already have, and the second one is country. In the Matrix, you can see we have a (+/-) sign, which allows you to expand the hierarchy from division to county when you click on it.
See this the below screenshot:
This option is called the “Drill Down” which means you can open and close the hierarchy according to the need you have to see the data in the Matrix. You can also use this option by right-click and then click on the drill-down option.
And in the same way, if you want to close the hierarchy, you can use the drill-down option again to do it.
Turn OFF Grand totals in Power BI Matrix
Turning off the grand totals can make your matrix look cleaner and less cluttered, especially when dealing with a complex matrix with multiple hierarchy levels.
- Open the Format Pane: After selecting the matrix, click the “Format” button. This will open the Format pane on the right side of your screen.
- Navigate to the Sub Totals Option: In the Format pane, look for the option labelled “Column subtotal” & “Row sutotals”. This option controls whether grand totals are displayed in your Matrix visualization.
- Turn Off Totals: Once you’ve found the option, you will see that it has a toggle button next to it. If grand totals are enabled for your matrix, this toggle will be set to ‘On’. Click on this toggle to change the setting from ‘On’ to ‘Off’. This will turn off the grand totals in your Matrix visualization.
Use Conditional Formatting Icons
In Power BI, Matrix supports conditional formatting, which you can use add icons based on the values.
- Click on this Matrix to select it, then click on the “Format” button in the “Visualization” pane to open the formatting settings.
- Find the “Cell elements” option in the Format pane, then turn on the “Icons” options.
- After that, click on the “Fx” icon to set up rules for displaying icons. You can specify the range of values for each icon, choose the icon type, and select the colors. You can also choose whether to show the icon only, the value only, or both.
- Once you apply the conditions you want to set, click ‘OK’ to apply the conditional icons to your Matrix.
Other Formatting Options for Matrix in Power BI
As we discussed earlier, there are several formatting options for a matrix in Power BI.
Some of these include:
- Grid: This option allows you to alter the style of the gridlines in the matrix, including their color and thickness.
- Row-Column Headers: This enables you to change the formatting of the row and column headers, such as the font color, background color, text size, and alignment.
- Values: In this section, you can change the formatting of the matrix values, such as the font color, background color, text size, and alignment.
- Subtotals/Grand Totals: This allows you to change the formatting of the subtotals and grand totals in the matrix.
- Field Value: The field value formatting options give you control over the look of individual fields in the matrix.
- Conditional Formatting: This powerful feature allows you to change the formatting of cells, rows, columns, and totals based on their values.
Key Features
- Row and Column Headers: These remain visible as you scroll, making it easier to read large matrices.
- Hierarchies: The Matrix supports hierarchies in rows and columns, letting you drill down into your data for detailed analysis.
- Subtotals and Grand Totals: Automatically calculate and display totals for your data at the grand total and subgroup levels.
- Drill-through: Set up drill-throughs to detailed reports based on the data point you focus on within the matrix.
- Conditional Formatting: You can easily add color scales, data bars, and icons to visualize data trends and exceptions.