How to Turn Off Grand Totals in Power BI Matrix [Row & Column]

A client asked me to clean up a Power BI matrix: they wanted the subtotals gone but the column grand total kept. If you’re wondering how to turn off the grand total in a Power BI matrix, or keep it and hide only the subtotals, it comes down to two settings in the Format pane.

In this tutorial, I’ll show you how to remove the row grand total and the column grand total, and how to show the grand total without subtotals.

Quick answer: Select the matrix, open Format visual and turn off Row subtotals to remove the row grand total, or Column subtotals to remove the column grand total. Power BI treats row subtotals and the row grand total as one setting. To keep the grand total but hide subtotals, turn on Per column level (or Per row level) and switch off the level you don’t want.

Power BI Matrix: Remove Subtotal and Show Column Grand Total Value

This works on any matrix visual. If you haven’t built one yet, start with how to create a matrix visual in Power BI and come back here.

Let’s see how to remove the subtotals of column values and display the grand total value of columns.

I have created the Matrix visual below using the sample data, which includes a ‘Year’ and a ‘Month’ column in the columns field of the matrix. The GIF below shows subtotals for specific years and the Grand Total in the Final column.

Now, my requirement is to only show the Grand Total value of a column by removing the subtotal value from the Power BI matrix table shown above.

Follow the steps to achieve this:

1. Open the Power BI Desktop report canvas, select the matrix visual, and then go to the Visualization pane. Then, click on Format your visual icon. Search for Column subtotals.

2. After that, Toggle On the Column subtotals option. Inside the Apply settings, turn on the Per column level option.
3. In the Series option, select the sublevel from the drop-down. Here, I have a Month column.
4. Finally, Toggle Off the ‘Show subtotal‘ option in the Columns section.

Then, you can get the expected result, as shown in the screenshot below. The image displays the column Grand Total value.

This is how to display the Grand Total column in a Power BI Matrix.

Check out Display Power BI Matrix Visual in Tabular Format

Power BI Matrix: Show Subtotals and Remove Matrix Column Grand Total

Now, let’s check on how to remove the column grand total value from a Matrix visual in Power BI.

Here, I will take the same matrix table that was used in the previous example. From the GIF below, I wanted to display only the subtotals of the columns and remove the grand total value from the matrix visual.

Check out the steps below:

1. On the Power BI report canvas -> Select the Matrix visual -> open the Column subtotals drop-down. Toggle on the ‘Per column level‘ option.
2. Then, under the Column level, select the highest-level column, not the lower-level one. In my case, it is the [Year] column.
3. Next, toggle off the “Show subtotal” option.

Output:

This will display the subtotal values of columns by removing the grand total from the matrix column in Power BI. Refer to the image below.

This way, you can remove the matrix column grand total of a Power BI Matrix visual.

Check out Power BI Matrix Visual Conditional Formatting

Remove Row Grand Total in Power BI Matrix Visual

In this section, I will explain how to disable the row total in a Power BI Matrix visual.

Let’s consider the same Matrix Visual, as shown below, where ‘Products‘ are on the Rows, Year and Month are on the Columns, and Values associated are with ‘Sales’.

From the image below, you can see that the Row Total is present at the bottom of the matrix visual.

Here, I do not want to show the Row grand total in a matrix.

Note:

Power BI considers row subtotals and grand totals as a single setting. So,you can not remove the Grand total of rows and subtotals separately, as they are linked.

Follow the steps mentioned:

1. On the Power BI report canvas, select the Matrix visual. Navigate to the Visualization pane and open the Format section. Expand the ‘Row subtotals‘ option.
2. Then, select ‘Series‘ with the highest hierarchy from the drop-down. Here, I’m selecting ‘Product‘ level.
3. Under the Rows section, Toggle off the ‘Show subtotal‘ option. Save the report by pressing Ctrl+s.

Output: Matrix without Row Total

I hope you can now understand how to disable the grand total and subtotals from the Matrix column. Additionally, learn how to disable the grand total from the matrix row in Power BI.

If you are still unsure about something, please feel free to submit your queries in the comment section below.

You may also like the following tutorials:

• Sort Column Values in Matrix Visual Power BI
• Set Column Width in Power BI Matrix Visual