ROLLUPADDISSUBTOTAL in DAX: Add Subtotals and Grand Totals

What Is ROLLUPADDISSUBTOTAL in DAX?

ROLLUPADDISSUBTOTAL is used with table functions such as
SUMMARIZECOLUMNS to create subtotal and grand total rows
for grouped columns.

Instead of returning only the detailed combinations of
Category and Subcategory, we can use
ROLLUPADDISSUBTOTAL to add higher-level totals to the result.

Complete DAX Query

// ROLLUP IS ADD SUBTOTAL

EVALUATE
    SUMMARIZECOLUMNS(
        ROLLUPADDISSUBTOTAL(
            'Product'[Category], "Grand Total",
            'Product'[Subcategory], "Sub Total"
        ), 
        FILTER('Product', 'Product'[Color] = "Red"),
        "Total Sales", SUM(Sales[Sales Amount])
    )

1. Understanding EVALUATE

Step-by-step explanation

Step 1: EVALUATE
EVALUATE is used to return the result of a table expression.
Here, the table expression is created by SUMMARIZECOLUMNS.

EVALUATE
    SUMMARIZECOLUMNS(
        ...
    )

Since SUMMARIZECOLUMNS returns a table, it can be used directly
after EVALUATE.

2. Understanding SUMMARIZECOLUMNS

SUMMARIZECOLUMNS is used to create a summarized table based on
grouping columns, filters, and calculated expressions.

In this query, it performs three main tasks:

1. Groups the data by Category and Subcategory.
2. Filters the Product table to only Red products.
3. Calculates Total Sales.

SUMMARIZECOLUMNS(
    Grouping Expression,
    Filter Expression,
    "Name", Calculation
)

3. Using ROLLUPADDISSUBTOTAL

Query

ROLLUPADDISSUBTOTAL(
    'Product'[Category], "Grand Total",
    'Product'[Subcategory], "Sub Total"
)

Step-by-step explanation

Step 1: Product[Category]
The first grouping column is Category.
DAX uses this column as the higher-level grouping in the hierarchy.

Step 2: “Grand Total”
This is the name of the Boolean column that indicates whether a row represents
the grand-total level for the specified hierarchy.

Step 3: Product[Subcategory]
The second grouping column is Subcategory.
This creates a more detailed level below Category.

Step 4: “Sub Total”
This is the name of the Boolean column that indicates whether a row represents
a subtotal level for the specified hierarchy.

4. Filtering Only Red Products

Query

FILTER(
    'Product',
    'Product'[Color] = "Red"
)

Step-by-step explanation

Step 1: FILTER(‘Product’)
The FILTER function starts with the Product table.

Step 2: Product[Color] = “Red”
The filter keeps only products whose Color value is
Red.

Therefore, the grouped result and the sales calculation are based on the
filtered product context for Red products.

5. Calculating Total Sales

Query

"Total Sales", SUM(Sales[Sales Amount])

Step-by-step explanation

Step 1: “Total Sales”
This is the name of the output column.

Step 2: SUM(Sales[Sales Amount])
SUM adds the values from Sales[Sales Amount]
for each grouping level generated by the query.

As a result, the query can show sales for individual
Subcategories, their Category subtotals,
and the overall Grand Total.

6. How the Result Is Structured

The query creates a hierarchy similar to:

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

7. Understanding the Subtotal Flags

One important feature of ROLLUPADDISSUBTOTAL is that it adds
Boolean columns based on the names provided in the function.

In this query, those columns are named:

• Grand Total
• Sub Total

These columns help identify whether a particular row belongs to a subtotal
or grand-total level of the rollup hierarchy.

8. Why Use ROLLUPADDISSUBTOTAL?

Without a rollup operation, a grouped query normally returns the combinations
of the grouping columns. With ROLLUPADDISSUBTOTAL, the result
can also contain higher-level subtotal and grand-total rows.

This is especially useful when creating DAX queries that need hierarchical
summaries such as:

• Category and Subcategory sales
• Year and Month summaries
• Region and Country totals
• Department and Employee summaries
• Other hierarchical business reports

Understanding the Complete Query

EVALUATE
    SUMMARIZECOLUMNS(
        ROLLUPADDISSUBTOTAL(
            'Product'[Category], "Grand Total",
            'Product'[Subcategory], "Sub Total"
        ), 
        FILTER('Product', 'Product'[Color] = "Red"),
        "Total Sales", SUM(Sales[Sales Amount])
    )

1. EVALUATE returns the final table.
2. SUMMARIZECOLUMNS creates the grouped summary.
3. ROLLUPADDISSUBTOTAL adds subtotal and grand-total levels.
4. Product[Category] defines the higher grouping level.
5. Product[Subcategory] defines the detailed grouping level.
6. FILTER keeps only Red products.
7. SUM(Sales[Sales Amount]) calculates Total Sales for each result level.

📥 Practice Data:
Download Practice Data Free

🎥 Prefer learning through video?
DAX YouTube Playlist