ROLLUP and Subtotals in DAX Using ROLLUPADDISSUBTOTAL and ADDMISSINGITEMS

What Is ROLLUP in DAX?

ROLLUP is used when we want to generate subtotal and grand total rows along with grouped data.
In this query, we are grouping sales by Product Category and adding a Grand Total row.

Query

// ROLLUP IS SUBTOTAL

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

Understanding the Query Structure

This query contains several nested functions. The easiest way to understand it is to start from the
inside and then move outward.

1. SUMMARIZECOLUMNS creates the grouped result.
2. ROLLUPADDISSUBTOTAL adds the subtotal or grand total level.
3. FILTER limits the data to Red products.
4. SUM calculates Total Sales.
5. ADDMISSINGITEMS adds missing combinations where required.
6. ROLLUPISSUBTOTAL identifies the subtotal/grand-total rows.

1. SUMMARIZECOLUMNS Creates the Grouped Result

Query

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

Step-by-step explanation

Step 1: SUMMARIZECOLUMNS
SUMMARIZECOLUMNS creates a summarized table based on the columns and expressions supplied to it.
Here, the result is grouped by Product Category.

Step 2: Product[Category]
The category column is used as the grouping column. Therefore, sales are calculated separately for each product category.

Step 3: Total Sales
The expression
SUM(Sales[Sales Amount])
calculates the total sales amount for each grouping.

2. Filtering Only Red Products

Query

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

Step-by-step explanation

Step 1: FILTER(‘Product’)
The Product table is supplied as the table to filter.

Step 2: Product[Color] = “Red”
Only products whose Color is equal to Red are included in the filter context.

Because this filter is passed into SUMMARIZECOLUMNS, the
Total Sales calculation is evaluated for the filtered product set.

3. ROLLUPADDISSUBTOTAL Adds the Grand Total

Query

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

Step-by-step explanation

Step 1: Product[Category]
This is the column on which the result is grouped.

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

The function therefore gives the summarized result an additional total level.

Conceptual result

The exact category names and sales values depend on the data in the model.
The table above illustrates the structure of the result.

4. Understanding the “Grand Total” Indicator

The second argument of ROLLUPADDISSUBTOTAL is
“Grand Total”.
This becomes an output column that indicates whether the current row belongs to the grand-total level.

This indicator is useful because a subtotal or grand-total row needs to be distinguishable from normal category rows.

5. ADDMISSINGITEMS

Query

ADDMISSINGITEMS(
    'Product'[Category],
    SUMMARIZECOLUMNS(
        ...
    ),
    ROLLUPISSUBTOTAL(
        'Product'[Category],
        [Grand Total]
    )
)

Step-by-step explanation

Step 1: Product[Category]
This identifies the grouping column for which missing items may need to be considered.

Step 2: SUMMARIZECOLUMNS result
The summarized table generated by SUMMARIZECOLUMNS is supplied as the main result.

Step 3: ROLLUPISSUBTOTAL
The final argument tells ADDMISSINGITEMS how the subtotal information should be interpreted.

The purpose of ADDMISSINGITEMS is to make sure missing combinations/items can be represented in the result
when the summarized query and its filters would otherwise remove them.

6. ROLLUPISSUBTOTAL Identifies the Subtotal Level

Query

ROLLUPISSUBTOTAL(
    'Product'[Category],
    [Grand Total]
)

Step-by-step explanation

Step 1: Product[Category]
This specifies the column associated with the rollup operation.

Step 2: [Grand Total]
This references the Boolean indicator created by
ROLLUPADDISSUBTOTAL.

Together, these arguments identify whether the current row represents the rollup level for
Product[Category].

7. Understanding the Complete Query

Now we can read the complete query from the inside out:

1. FILTER keeps only Red products.
2. SUMMARIZECOLUMNS groups the filtered data by Product Category.
3. SUM(Sales[Sales Amount]) calculates Total Sales.
4. ROLLUPADDISSUBTOTAL adds the grand-total level.
5. Grand Total identifies whether the row is the total level.
6. ROLLUPISSUBTOTAL identifies the rollup level for Product Category.
7. ADDMISSINGITEMS adds missing items according to the grouping and rollup definition.
8. EVALUATE returns the final table.

8. Complete Query with Comments

// ROLLUP IS SUBTOTAL

EVALUATE

    // Add missing category items to the final result
    ADDMISSINGITEMS(

        // Grouping column
        'Product'[Category],

        // Create the summarized result
        SUMMARIZECOLUMNS(

            // Add the grand-total level
            ROLLUPADDISSUBTOTAL(
                'Product'[Category],
                "Grand Total"
            ),

            // Analyze only Red products
            FILTER(
                'Product',
                'Product'[Color] = "Red"
            ),

            // Calculate sales
            "Total Sales",
            SUM(Sales[Sales Amount])
        ),

        // Identify the rollup/subtotal level
        ROLLUPISSUBTOTAL(
            'Product'[Category],
            [Grand Total]
        )
    )

ROLLUP Functions at a Glance

📥 Practice Data:
Download Practice Data Free

🎥 Prefer learning through video?
DAX YouTube Playlist