How to Add Subtotals in Power BI

Displaying summary values in your reports is a fantastic way to grasp the performance of different categories at a glance. Adding subtotals in Power BI is a common requirement for anyone building a report, but navigating all the formatting options and custom calculations can sometimes feel like a chore. This article will guide you through adding and customizing subtotals in your Power BI visuals, from a simple toggle switch to crafting custom logic with DAX.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why Subtotals Matter in Your Reports

Before jumping into the "how," it helps to appreciate the "why." Subtotals are more than just numbers on a page, they provide critical context and structure to your data. Imagine a sales report that lists every single product sold across multiple regions. While the detail is important, it's hard to see the big picture. Subtotal are more than just numbers on a page, they provide critical context and structure to your data. Imagine a sales report that lists every single product sold across multiple regions. While the detail is important, it's hard to see the big picture. Subtotals solve this by:

• Summarizing Categories: They instantly show you the total sales for the "East" region or the total revenue from the "Electronics" category, without forcing you to export the data and wrangle it in a spreadsheet.
• Improving Readability: Subtotals break up long tables and matrices into logical, easy-to-digest chunks. This visual separation helps anyone reading the report to quickly identify high- and low-performing segments.
• Facilitating Comparison: With clear subtotals for each group, you can easily compare the performance of different regions, product lines, or sales reps side-by-side.

In short, they turn a dense wall of data into an organized, insightful summary that tells a more complete story.

Adding Subtotals in a Matrix Visual: Step-by-Step

The matrix visual is Power BI's go-to for hierarchical data, making it the perfect candidate for adding subtotals. Its structure is similar to a pivot table in Excel, allowing you to nest categories and see them roll up. Here’s how to enable subtotals in just a few clicks.

1. Set Up Your Matrix Visual

First, add a Matrix visual to your report canvas. For this example, let's create a simple sales report. We'll drag our data fields into the "Visualizations" pane:

• Rows: Drag in a couple of hierarchical fields, like Region and then Product Category. Power BI will automatically create a hierarchy, allowing users to drill down.
• Values: Drag in a numerical field, like Sales Amount.

You’ll now have a basic matrix showing sales for each product category, grouped by region.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

2. Locate and Enable Row Subtotals

With your matrix visual selected, navigate to the Format your visual pane (the icon that looks like a paintbrush).

• Scroll down and expand the Row subtotals section.
• You'll see a simple toggle switch. Click it to turn row subtotals On.

Just like that, Power BI adds a total line for each region in your matrix. This subtotal is the sum of all product categories within that region.

3. Fine-Tune with "Per row level"

Sometimes you have multiple levels in your hierarchy (e.g., Region > Country > City) but only want to see subtotals for Regions and not for Countries. The "Per row level" option gives you this granular control.

• After enabling row subtotals, a new section named Options will appear. Expand it.
• Turn on the Per row level toggle.
• You will now see each field from your "Rows" well listed with its own on/off switch. You can now choose exactly which levels of the hierarchy should display a subtotal. This is incredibly useful for cleaning up complex reports.

Making Your Subtotals Stand Out: Formatting Options

Now that your subtotals are visible, the next step is to make them easy to identify. Clear formatting guides the reader’s eye and prevents them from mistaking a subtotal for just another line item. All these options are found in the same Format your visual > Row subtotals section.

Change the Label Text

By default, Power BI labels your subtotal row as "Total." This can be a bit generic, especially in a matrix with multiple levels. You can make it more descriptive.

• Expand the Row subtotals menu in the format pane.
• In the Subtotal label text box, you can type your own custom text. For our example, changing it to "Region Total" makes the report much clearer.

Adjust Fonts and Colors

One of the easiest ways to make subtotals pop is by changing their appearance. You can modify the font color, background color, and styling to match your report's design or simply to increase contrast.

• In the Values section within the Row subtotals card, you can set the font to be bold or italic.
• Use the Font color and Background color selectors to visually distinguish the subtotal rows. A common practice is to use a light grey background or a bold font to separate them from the detail rows.

Position Subtotals at the Top or Bottom

By default, subtotals appear at the bottom of each group. However, you can move them to the top if that format works better for your analysis.

• Find the Position dropdown within the Row subtotals menu.
• You can choose between Bottom (default) and Top.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Level Up Your Analysis: Conditional Formatting for Subtotals

Basic formatting is good, but conditional formatting is where you can start adding powerful analytical insights directly into your visuals. What if you wanted to instantly see which regional subtotals are above a certain goal? You don't need a separate chart for that, you can color-code your subtotals.

Here’s the trick: Power BI has a specific setting to apply conditional formatting to only totals and subtotals, leaving the detail rows alone.

1. On the Visualizations pane, right-click the field in the Values well (e.g., Sales Amount).
2. In the context menu, select Conditional formatting and choose an option like Background color.
3. A dialog window will appear. Here, you can define your rules. For instance, you could set up a rule: If value is greater than 500,000, then color is green.
4. Here's the most important step: At the bottom of this dialog box, find the Apply to dropdown. Change it from "Values only" to Totals only.

Once you click OK, only your subtotal and grand total rows will be color-coded based on your rule. This is a game-changer for building scannable, data-rich reports that quickly highlight key performance indicators at a summary level.

When Standard Subtotals Aren't Enough: Creating Custom Totals with DAX

There are times when the standard aggregation (like SUM or AVERAGE) supplied by Power BI doesn't fit your needs. Perhaps you need your subtotal to calculate something entirely different from the values in your detail rows. For instance, you might want to show the SUM of sales per category, but for the regional subtotal, you need to see the AVERAGE monthly sales for that region.

This is where DAX (Data Analysis Expressions) comes in. With a simple DAX measure, you can command Power BI to behave differently for a subtotal row versus a detail row.

The key is a function called HASONEVALUE(). This function checks if a column is currently being filtered by a single, specific value. In a matrix, this will be TRUE for the detail rows (e.g., the 'Clothing' category row) and FALSE for the subtotal rows (which represent multiple categories).

Let's create a DAX measure that shows the SUM for categories but an AVERAGE for subtotals.

1. From the Home ribbon, click New measure.
2. Enter the following DAX formula into the formula bar:

Sales (SUM or AVG Subtotal) = 
IF(
    HASONEVALUE('Product Table'[Product Category]),
    SUM('Sales Table'[Sales Amount]),
    AVERAGEX(VALUES('Product Table'[Product Category]), CALCULATE(SUM('Sales Table'[Sales Amount])))
)

Let's break that down in plain English:

• The IF statement checks a condition.
• That condition is HASONEVALUE('Product Table'[Product Category]). It asks, "Are we currently looking at a single product category?"
• If TRUE (we are on a detail row for a single category like 'Accessories'), the formula returns the simple SUM of sales.
• If FALSE (we are on a subtotal row for a region, which contains multiple categories), it calculates the AVERAGE sales amount across those categories.

Once you create this measure, simply drag it into the Values well of your matrix instead of the original Sales Amount field. Now, your subtotals will magically display a different calculation, giving you a custom, deeply insightful view of your data.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Troubleshooting Common Subtotal Issues

Even with simple features, you can occasionally run into bumps. Here are a couple of common issues and how to fix them.

My Subtotals Aren't Showing Up!

This almost always comes down to two things:

• The Row subtotals toggle in the format pane got switched off. Double-check that it's enabled.
• For subtotals to exist, you need a hierarchy. If you only have one field in the "Rows" well of your matrix, there are no groups to subtotal. Make sure you have at least two fields in the "Rows" section.

My DAX Total Isn't Behaving Correctly

If your custom DAX subtotal is showing a strange result or simply repeating the grand total for every subgroup, the problem usually lies in your understanding of filter context. Your HASONEVALUE() or ISINSCOPE() function may be checking the wrong column, or the logic for the "total" part of your IF statement might not be correctly defined to handle the broader filter context of a subtotal.

Final Thoughts

You've now seen how to master subtotals in Power BI, from enabling the default options in a matrix visual to formatting them for clarity and even creating custom summary calculations using DAX. These features empower you to transform granular data into structured reports that are both easy to understand and rich with analytical detail.

Building reports in tools like Power BI is a powerful skill, but it often involves hunting through formatting panes and spending hours arranging visuals. At Graphed, we’ve made this entire experience simpler. You can create real-time reports and dashboards instantly just by describing what you want to see. Instead of manually adding and styling subtotals, you can just ask, "Show me sales by region and category with subtotals for each region," and Graphed will build the visual for you, live-connected to your analytics, sales, and marketing data sources.