ROLLUPADDISSUBTOTAL DAX Function (Table manipulation)

Identifies a subset of columns specified in the call to SUMMARIZECOLUMNS function that should be used to calculate groups of subtotals.

Syntax

ROLLUPADDISSUBTOTAL ( [<GrandtotalFilter>], <GroupBy_ColumnName>, <Name> [, [<GroupLevelFilter>] [, <GroupBy_ColumnName>, <Name> [, [<GroupLevelFilter>] [, … ] ] ] ] )
Parameter Attributes Description
GrandtotalFilter Optional

Filter to be applied to the grandtotal level.

GroupBy_ColumnName Repeatable

A column to be returned.

Name Repeatable

A column name to be added.

GroupLevelFilter Optional
Repeatable

Filter to be applied to the current level.

Return values

The function does not return a value. It only specifies the set of columns to be subtotaled.

Remarks

The ROLLUPADDISSUBTOTAL function is used exclusively within SUMMARIZECOLUMNS.

The addition of the ROLLUPADDISSUBTOTAL() syntax modifies the behavior of the SUMMARIZECOUMNS function by adding roll-up/subtotal rows to the result based on the groupBy_columnName columns.

» 1 related article
» 2 related functions

Examples

--  ROLLUPADDISSUBTOTAL computes subtotals for the specified
--  columns and adds a new column to the result indicating 
--  whether the current row is a subtotal or not.
EVALUATE
SUMMARIZECOLUMNS (
    ROLLUPADDISSUBTOTAL ( 'Date'[Calendar Year], "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]
Calendar Year Year total Amount
2008-01-01 false 747,365.53
2009-01-01 false 362,354.56
(Blank) true 1,109,720.09
--  You can specify multiple ROLLUPADDISSUBTOTAL in the same query
--  to produce matrices of subtotals.
EVALUATE
SUMMARIZECOLUMNS (
    ROLLUPADDISSUBTOTAL ( 'Date'[Calendar Year], "Year total" ),
    ROLLUPADDISSUBTOTAL ( Customer[Education],   "Education 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 total], [Education]
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
2008-01-01 (Blank) false true 747,365.53
2009-01-01 Bachelors false false 189,037.54
2009-01-01 Partial College false false 173,317.03
2009-01-01 (Blank) false true 362,354.56
(Blank) Bachelors true false 618,591.67
(Blank) Partial College true false 491,128.43
(Blank) (Blank) true true 1,109,720.09
--  Alternatively, you can use multiple ROLLUP columns in the same
--  ROLLUPADDISSUBTOTAL to reduce the number of subtotals generated
--  including only the group totals at higher levels following the 
--  hierarchy of columns specified.

-- The first result has subtotals by Year and then Education (no Education total)
EVALUATE
SUMMARIZECOLUMNS (
    ROLLUPADDISSUBTOTAL ( 
        'Date'[Calendar Year], "Year total",
        Customer[Education],   "Education 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 total], [Education]

-- The second result has subtotals by Education and then Year (no Year total)
EVALUATE
SUMMARIZECOLUMNS (
    ROLLUPADDISSUBTOTAL ( 
        Customer[Education],   "Education total",
        'Date'[Calendar Year], "Year total"
    ),
    TREATAS ( { "CY 2008", "CY 2009" }, 'Date'[Calendar Year] ),
    TREATAS ( { "Bachelors", "Partial College" }, Customer[Education] ),
    "Amount", [Sales Amount]
)
ORDER BY [Year total], [Education], [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
2008-01-01 (Blank) false true 747,365.53
2009-01-01 Bachelors false false 189,037.54
2009-01-01 Partial College false false 173,317.03
2009-01-01 (Blank) false true 362,354.56
(Blank) (Blank) true true 1,109,720.09
Education Calendar Year Education total Year total Amount
Bachelors 2008-01-01 false false 429,554.13
Bachelors 2009-01-01 false false 189,037.54
Partial College 2008-01-01 false false 317,811.40
Partial College 2009-01-01 false false 173,317.03
(Blank) (Blank) true true 1,109,720.09
Bachelors (Blank) false true 618,591.67
Partial College (Blank) false true 491,128.43
--  Using ROLLUPGROUP you can further reduce the number of subtotals
--  by grouping together several columns.
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 articles

Learn more about ROLLUPADDISSUBTOTAL in the following articles:

  • Introducing SUMMARIZECOLUMNS

    This article explains how to use SUMMARIZECOLUMNS, which is a replacement of SUMMARIZE and does not require the use of ADDCOLUMNS to obtain good performance. » Read more

Related functions

Other related functions are:

Last update: Apr 2, 2021   » Contribute   » Show contributors

Contributors: Alberto Ferrari, Marco Russo

2018-2021 © SQLBI. All rights are reserved. Information coming from Microsoft documentation is property of Microsoft Corp. » Contact us   » Privacy Policy & Cookies

Context Transition

This function performs a Context Transition if called in a Row Context. Click to read more.

Row Context

This expression is executed in a Row Context. Click to read more.

Iterator

Not recommended

The use of this function is not recommended. See Remarks and Related functions for alternatives.

Not recommended

The use of this parameter is not recommended.

Deprecated

This function is deprecated. Jump to the Alternatives section to see the function to use.

Deprecated

This parameter is deprecated and its use is not recommended.

DirectQuery compatibility

Limitations are placed on DAX expressions allowed in measures and calculated columns.
The state below shows the DirectQuery compatibility of the DAX function.

Contribute

Want to improve the content of ROLLUPADDISSUBTOTAL? Did you find any issue?
Please, report it us! All submissions will be evaluated for possible updates of the content.


This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.