Learn how to easily turn off Power BI Subtotals and selectively disable multiple column subtotals in a Matrix Visual. Power BI provides developers with a lot of granularity of which totals and subtotals to show on their visualizations.
If you’ve ever had a Matrix visual with multiple subtotals show up like the screenshot below, you’ll know what we’re talking about. It can be highly annoying and makes little sense as to why Power BI gives you multiple subtotal columns.
Luckily, it’s a fairly quick fix.
To disable subtotals in Power BI, select a visual. Navigate to the “Format Your Visual” section of the visualization pane. Turn off “Column Subtotals” to disable all subtotals. Enable “Per Column Level” to change individual subtotals. This setting allows you to choose each column and selectively turn it on or off.
Table of Contents
Before going into more detail of how to work with subtotals in Power BI, let’s dive into the reasons why Power BI shows multiple subtotal columns.
Why does Power BI show Multiple Subtotals in a Matrix or Table Visual?
Power BI will automatically generate a subtotal for each field that is added into column. When working with an expanded Date Hierarchy, Power BI will display totals for the Year, Quarter, Month, and Day.
Power BI’s presentation of date hierarchies specifically makes more sense when you consider that sometimes you want to show things like monthly, quarterly and annual subtotals when a matrix visual is expanded.
For example, this matrix visual shows month subtotals for quarters along with a full year total at the far right of the visual.
To some extent, this is the same behavior that you’ll see when working in an Excel pivot table to summarize data by dates and expand out each month. Excel pivot tables are a little easier to read because the column totals have different formatting to differentiate them from grand totals.
How To Disable Grand Totals and Subtotals in Power BI
Power BI Totals and Subtotals are controlled at the individual visualization level. They are updated under the Format Your Visual section of the visualizations panel. The icon looks like a small chart and paintbrush.
Towards the bottom of the list is a selection for Column Subtotals and Row Subtotals along with Column and Row Grand Totals.
Tip: If you have difficulty finding formatting settings in Power BI, we highly recommend using the Search box. It’s the fastest way to find settings buried under sub-menus.
Disable all subtotals by unchecking the Column Subtotals Option
How to Selectively Disable Specific Subtotals in Power BI
To have more control over which subtotals show, and which ones are turned off turn on per column level subtotals. Navigate to the Format Your Visual section of the visualizations panel, and instead of turning column subtotals off, leave it on.
Expand the Column Subtotals section by clicking the arrow at the right of the format setting.
Enable Per Column Level
This gives you a drop down menu of each column that a subtotal is being calculated for.
Use the dropdown box to select a column name.
After selecting a column, a box below will light up.
This is where you can click Show Subtotal and change the name of the subtotal column that you previously selected from the dropdown menu.
Note: You may have to disable more than one column level. Go back to the column level dropdown box, and change the Show Subtotal box below for each column from the dropdown.
While we used columns and date hierarchies as our example, the same method applies for controlling individual row subtotals. If you are working with multiple measures or columns in a visual, each one may have its own subtotal.
Updating Subtotal Formatting in Power BI
Power BI provides two methods for formatting grand totals and subtotals with the Matrix visual. You can either update formatting for row and column grand totals, or you can use specific column formatting.
In the following example, we applied a background color to the grand total.
Grand total formatting is updated under the Column Grand Total section of the visualization format panel.
You can modify the following items:
- Font
- Text Color
- Background Color
- Apply formatting to labels / column titles
A second option is to use the Specific Column settings.
Specific Columns formatting is our preferred method because it gives you greater control over formatting than what you get with grand total formatting.
Specific Column formatting gives you many more options than what is available under grand totals.
- Apply to Header
- Apply to Subtotals only
- Apply to Grand Total
- Apply to Values
- Modify Text Color, Alignment, and Decimal Places
Updating Grand Total settings is a fast and efficient way to give your matrix visual some added depth. Using the Specific Column settings allows you to differentiate the formatting of subtotals from a grand total. Using this method will allow you to format a matrix more similar to a pivot table.
In situations where you want to show multiple subtotals and a grand total, formatting updates can make the difference between making your visual easily readable vs. creating confusion when users see multiple subtotals on a page.
Try giving subtotals custom titles along with formatting differences to avoid confusion.
As an added bonus, there are a lot of crazy things you can do in DAX to customize Matrix visualization formatting for even more advanced formatting techniques, check out this video:
Conclusion
We hope this makes working with row and column subtotals easier. Power BI provides you with the option to turn off grand totals and subtotals all together, or you can selective turn off multiple subtotals using the Per Column Level settings under the visualization formatting pane.
While we used the Matrix visual in our examples today, these same concepts apply to a Power BI table and work for both column subtotals and row subtotals.
For instances where you want to show multiple levels of subtotals, add custom column header names along with custom formatting using the Specific Column settings to visually differentiate levels of subtotals and grand totals to avoid user confusion.