Understanding ROLLUP in DAX: Subtotals and Grand Totals with SUMMARIZE

September 23, 2026 92 visits

In this article, we will learn how to use ROLLUP with
SUMMARIZE in DAX to generate grouped results along with
subtotals and grand totals. This is especially useful when
building summary tables for Power BI analysis and reporting.

📥 Practice Data:
Download Practice Data Free

🎥 Prefer learning through video?
DAX YouTube Playlist

What Is ROLLUP in DAX?

ROLLUP is used inside functions such as SUMMARIZE
to create grouped results together with subtotal and grand total rows.
Instead of returning only the individual groups, ROLLUP allows DAX to add higher-level
totals automatically.

In this example, the Sales table is summarized by product
Category and Subcategory, while
Sales Amount is aggregated as Total Sales.

1. ROLLUP with a Single Column

Query

DAX Query
EVALUATE
	SUMMARIZE(
		Sales,
		ROLLUP('Product'[Category]),
		"Total Sales", SUM(Sales[Sales Amount])
	)

Step-by-step explanation

Step 1: EVALUATE
EVALUATE returns the result of the table expression as the
output of the DAX query.

Step 2: SUMMARIZE
SUMMARIZE creates a summary table by grouping the Sales data
based on the columns provided.

Step 3: Sales
Sales is the table from which the data is summarized.

Step 4: ROLLUP(‘Product'[Category])
The Category column is used as the grouping column.
ROLLUP additionally creates a higher-level total row for all
categories combined.

Step 5: “Total Sales”, SUM(Sales[Sales Amount])
This creates a calculated column named Total Sales by summing
the Sales Amount for each grouping level.

What will you get?

The result contains one row for each Category and an additional
grand total row.

Category Total Sales
Accessories 125,000
Bikes 4,500,000
Clothing 850,000
Grand Total 5,475,000

The values above are for illustration. Your actual result will depend on the
data available in your Sales and Product tables.

Key takeaway:
ROLLUP(‘Product'[Category]) groups the data by Category and
adds a grand total for all categories.

2. ROLLUP with Category and Subcategory

Query

DAX Query
EVALUATE
	SUMMARIZE(
		Sales,
		ROLLUP('Product'[Category], 'Product'[Subcategory]),
		"Total Sales", SUM(Sales[Sales Amount])
	)

Step-by-step explanation

Step 1: EVALUATE
EVALUATE returns the summary table generated by
SUMMARIZE.

Step 2: SUMMARIZE
SUMMARIZE creates a grouped table from the
Sales table.

Step 3: ROLLUP(‘Product'[Category], ‘Product'[Subcategory])
Here, Category is the higher-level grouping and
Subcategory is the lower-level grouping.
ROLLUP creates the detailed combinations and the higher-level
subtotal and grand total rows.

Step 4: “Total Sales”, SUM(Sales[Sales Amount])
The Sales Amount is summed for every grouping level generated
by the ROLLUP operation.

What will you get?

The result can contain three levels of aggregation:

  1. Category + Subcategory — detailed grouped sales.
  2. Category only — subtotal for each Category.
  3. Grand Total — total sales across all Categories and Subcategories.
Category Subcategory Total Sales
Bikes Mountain Bikes 1,500,000
Bikes Road Bikes 2,000,000
Bikes Touring Bikes 1,000,000
Bikes Subtotal 4,500,000
Clothing Jerseys 500,000
Clothing Shorts 350,000
Clothing Subtotal 850,000
Grand Total 5,475,000

The result table above is only an illustration of the structure produced by
the query. The actual categories, subcategories, and sales values will depend
on your practice data.

Key takeaway:
When multiple columns are supplied to ROLLUP, DAX can generate
detailed rows, category-level subtotals, and an overall grand total.

Understanding the Difference Between the Two Queries

ROLLUP Expression Grouping Level Additional Total
ROLLUP(‘Product'[Category]) Category Grand Total
ROLLUP(‘Product'[Category], ‘Product'[Subcategory]) Category + Subcategory Category Subtotals + Grand Total

How ROLLUP Works with Hierarchical Data

A useful way to understand the second query is to think about the hierarchy:

Hierarchy
Category
	└── Subcategory

The most detailed level is Category + Subcategory.
ROLLUP then moves upward through the hierarchy to calculate the
Category subtotal and finally the Grand Total.

Important:
The order of columns inside ROLLUP matters because the
columns represent different levels of the grouping hierarchy.

📝 Practice Exercise

Try modifying the query to summarize the Sales table using
Product Category, Product Subcategory,
and Total Sales.

Then observe the result carefully and identify:

  • The detailed Category and Subcategory rows.
  • The subtotal generated for each Category.
  • The final Grand Total row.

Practice Query

DAX Query
EVALUATE
	SUMMARIZE(
		Sales,
		ROLLUP('Product'[Category], 'Product'[Subcategory]),
		"Total Sales", SUM(Sales[Sales Amount])
	)

Conclusion

In this article, we learned how ROLLUP works with
SUMMARIZE in DAX to generate grouped results with
subtotals and grand totals.

  • ROLLUP adds higher-level aggregation rows to grouped results.
  • ROLLUP(Category) provides Category-level results and a Grand Total.
  • ROLLUP(Category, Subcategory) provides detailed rows, Category subtotals, and a Grand Total.
  • The order of columns inside ROLLUP defines the grouping hierarchy.
  • SUMMARIZE can use the ROLLUP result to create useful summary tables for analysis.

Understanding ROLLUP is important when you need DAX queries
that reproduce hierarchical reporting structures with multiple levels of
aggregation.

📥 Practice Data:
Download Practice Data Free

🎥 Prefer learning through video?
DAX YouTube Playlist