ROLLUPGROUP DAX Function (Table manipulation)
Identifies a subset of columns specified in the call to SUMMARIZE function that should be used to calculate groups of subtotals.
Syntax
| Parameter | Attributes | Description |
|---|---|---|
| GroupBy_ColumnName | Repeatable |
A column to be returned. |
Return values
The function does not return a value. It only specifies the set of columns to be subtotaled.
Remarks
The ROLLUP function is used exclusively within SUMMARIZE or ADDMISSINGITEMS.
ROLLUPGROUP can be used to calculate groups of subtotals. If used within SUMMARIZE in-place of ROLLUP, ROLLUPGROUP will yield the same result by adding roll-up rows to the result on the GroupBy_ColumnName columns. However, the addition of ROLLUPGROUP() inside a ROLLUP syntax can be used to prevent partial subtotals in roll-up rows.
Examples
-- Using ROLLUPGROUP in SUMMARIZE you can reduce the number
-- of subtotals by grouping together several columns.
EVALUATE
CALCULATETABLE (
SUMMARIZE (
Sales,
ROLLUP ( ROLLUPGROUP ( 'Date'[Calendar Year], Customer[Education] ) ),
"Year total", ISSUBTOTAL ( 'Date'[Calendar Year] ),
"Education total", ISSUBTOTAL ( Customer[Education] ),
"Amount", [Sales Amount]
),
TREATAS ( { "CY 2008", "CY 2009" }, 'Date'[Calendar Year] ),
TREATAS ( { "Bachelors", "Partial College" }, Customer[Education] )
)
ORDER BY
[Year total],
[Calendar Year]
| Calendar Year | Education | Year total | Education total | Amount |
|---|---|---|---|---|
| 2008-01-01 | Bachelors | false | false | 429,554.13 |
| 2008-01-01 | Partial College | false | false | 317,811.40 |
| 2009-01-01 | Bachelors | false | false | 189,037.54 |
| 2009-01-01 | Partial College | false | false | 173,317.03 |
| (Blank) | (Blank) | true | true | 1,109,720.09 |
-- ROLLUPGROUP can also be used in SUMMARIZECOLUMNS with the same
-- goal: grouping columns together to reduce the number of subtotals.
EVALUATE
SUMMARIZECOLUMNS (
ROLLUPADDISSUBTOTAL (
ROLLUPGROUP ( 'Date'[Calendar Year], Customer[Education] ),
"Year total"
),
TREATAS ( { "CY 2008", "CY 2009" }, 'Date'[Calendar Year] ),
TREATAS ( { "Bachelors", "Partial College" }, Customer[Education] ),
"Amount", [Sales Amount]
)
ORDER BY
[Year total],
[Calendar Year],
[Education]
| Calendar Year | Education | Year total | Amount |
|---|---|---|---|
| 2008-01-01 | Bachelors | false | 429,554.13 |
| 2008-01-01 | Partial College | false | 317,811.40 |
| 2009-01-01 | Bachelors | false | 189,037.54 |
| 2009-01-01 | Partial College | false | 173,317.03 |
| (Blank) | (Blank) | true | 1,109,720.09 |
Related functions
Other related functions are:
Last update: Nov 14, 2024 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo
Microsoft documentation: https://docs.microsoft.com/en-us/dax/rollupgroup-function-dax