SUMMARIZE function with Options

Description

Read DAX SUMMARIZE function before reading this variant.

You have the following advanced options that you can use within SUMMARIZE function −

  • ROLLUP function
  • ROLLUPGROUP function
  • ISSUBTOTAL function

When you use these functions within SUMMARIZE function, you will get different results.

  • If you use ROLLUP function or ROLLUPGROUP function, the behavior of the SUMMARIZE function is modified by adding roll-up rows to the result on the groupBy_columnName columns.

  • If you use ROLLUPGROUP function within ROLLUP function, you can prevent partial subtotals in roll-up rows.

  • If you use ISSUBTOTAL function within expression part of SUMMARIZE function, you will create another column with logical values returned by ISSUBTOTAL in the resulting table. The value will be TRUE, if the row contains sub-total values for the column given as parameter to ISSUBTOTAL function. FALSE, otherwise.

Syntax

SUMMARIZE (<table>, <groupBy_columnName>, [<groupBy_columnName>] …, 
   [ROLLUP (<groupBy_columnName>, [<groupBy_columnName> …])],
   [<name>, <expression>] …) 

SUMMARIZE (<table>, <groupBy_columnName>, [<groupBy_columnName>] …, 
   [ROLLUPGROUP (<groupBy_columnName>, [<groupBy_columnName> …])],
   [<name>, <expression>] …) 

SUMMARIZE (<table>, <groupBy_columnName>, [<groupBy_columnName>] …, 
   [ROLLUP (ROLLUPGROUP (<groupBy_columnName>, [<groupBy_columnName>] …))],
   [<name>, <expression>] …)

SUMMARIZE (<table>, <groupBy_columnName>, [<groupBy_columnName>] …, 
   [ROLLUP (<groupBy_columnName>, [<groupBy_columnName> …])],
   [<name>, {<expression> | ISSUBTOTAL (<columnName>)}] …)

Parameters (ROLLUP / ROLLUPGROUP Function)

Sr.No. Parameter & Description
1

groupBy_columnName

The qualified name of an existing column to be used to create summary groups based on the values found in it. This parameter cannot be an expression.

Parameters (ISSUBTOTAL Function)

Sr.No. Parameter & Description
1

columnName

The name of any column in the table of the SUMMARIZE function or any column in a related table to table.

The other parameters for SUMMARIZE function are as explained in DAX SUMMARIZE Function.

Return Value

A table with the selected columns for the groupBy_columnName parameters and the summarized columns designed by the name parameters and additionally, the roll-up rows to the groupBy_columnName columns. Subtotals are not displayed if ROLLUPGROUP is used within ROLLUP.

If SUBTOTAL function is used −

An additional column, with TRUE if the row contains a sub-total value for the column given as parameter, with FALSE, otherwise.

Remarks

The columns mentioned in the ROLLUP function cannot be referenced as groupBy_columnName parameters of SUMMARIZE function.

ROLLUP function can be used only as a parameter for SUMMARIZE function and nowhere else.

ROLLUPGROUP function can be used only as a parameter for the following and nowhere else.

  • SUMMARIZE function, or
  • ROLLUP function

ISSUBTOTAL function can only be used in the expression part of SUMMARIZE function.

ISSUBTOTAL must be preceded by a matching name column

Example - ROLLUP

= SUMMARIZE ( 
   SalesTarget, ROLLUP (SalesTarget[SalespersonID]), 
   SalesTarget[SalesTarget],"MaxTarget",MAX (SalesTarget[SalesTarget])
)

Example – ROLLUP with ROLLUPGROUP

= SUMMARIZE ( 
   SalesTarget,ROLLUP (ROLLUPGROUP (SalesTarget[SalespersonID])),
      SalesTarget[SalesTarget],"MaxTarget",MAX(SalesTarget[SalesTarget])
)

Example - ISSUBTOTAL

= SUMMARIZE ( 
   SalesTarget,ROLLUP (ROLLUPGROUP (SalesTarget[SalespersonID])), 
      SalesTarget[SalesTarget],"MaxTarget",MAX (SalesTarget[SalesTarget]), 
      "IsSubTotalSalesTarget", ISSUBTOTAL (SalesTarget[SalesTarget])
)
dax_functions_aggregation.htm
Advertisements