TechNet
Export (0) Print
Expand All

SUMMARIZE Function (DAX)

 

Returns a summary table for the requested totals over a set of groups.

Syntax

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

Parameters

table
Any DAX expression that returns a table of data.

groupBy_columnName
(Optional) 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.

name
The name given to a total or summarize column, enclosed in double quotes.

expression
Any DAX expression that returns a single scalar value, where the expression is to be evaluated multiple times (for each row/context).

Return Value

A table with the selected columns for the groupBy_columnName arguments and the summarized columns designed by the name arguments.

Remarks

  1. Each column for which you define a name must have a corresponding expression; otherwise, an error is returned. The first argument, name, defines the name of the column in the results. The second argument, expression, defines the calculation performed to obtain the value for each row in that column.

  2. groupBy_columnName must be either in table or in a related table to table.

  3. Each name must be enclosed in double quotation marks.

  4. The function groups a selected set of rows into a set of summary rows by the values of one or more groupBy_columnName columns. One row is returned for each group.

Example

The following example returns a summary of the reseller sales grouped around the calendar year and the product category name, this result table allows you to do analysis over the reseller sales by year and product category.

SUMMARIZE(ResellerSales_USD
      , DateTime[CalendarYear]
      , ProductCategory[ProductCategoryName]
      , "Sales Amount (USD)", SUM(ResellerSales_USD[SalesAmount_USD])
      , "Discount Amount (USD)", SUM(ResellerSales_USD[DiscountAmount])
      )

The following table shows a preview of the data as it would be received by any function expecting to receive a table:

DateTime[CalendarYear]ProductCategory[ProductCategoryName][Sales Amount (USD)][Discount Amount (USD)]
2008Bikes12968255.4236167.6592
2005Bikes6958251.0434231.1621
2006Bikes18901351.08178175.8399
2007Bikes24256817.5276065.992
2008Components2008052.70639.9266
2005Components574256.98650
2006Components3428213.05948.7674
2007Components5195315.2164226.0444
2008Clothing366507.8444151.1235
2005Clothing31851.162890.9593
2006Clothing455730.97294233.039
2007Clothing815853.286812489.3835
2008Accessories153299.924865.5945
2005Accessories18594.47824.293
2006Accessories86612.74631061.4872
2007Accessories275794.84034756.6546

Advanced SUMMARIZE options

SUMMARIZE with ROLLUP

The addition of the ROLLUP() syntax modifies the behavior of the SUMMARIZE function by adding roll-up rows to the result on the groupBy_columnName columns.

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

ROLLUP parameters

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.

Note: All other SUMMARIZE parameters are explained before and not repeated here for brevity.

Remarks

  • The columns mentioned in the ROLLUP expression cannot be referenced as part of a groupBy_columnName columns.

Example

The following example adds roll-up rows to the Group-By columns of the SUMMARIZE function call.

SUMMARIZE(ResellerSales_USD
      , ROLLUP( DateTime[CalendarYear], ProductCategory[ProductCategoryName])
      , "Sales Amount (USD)", SUM(ResellerSales_USD[SalesAmount_USD])
      , "Discount Amount (USD)", SUM(ResellerSales_USD[DiscountAmount])
)

The following table shows a preview of the data as it would be received by any function expecting to receive a table:

DateTime[CalendarYear]ProductCategory[ProductCategoryName][Sales Amount (USD)][Discount Amount (USD)]
2008Bikes12968255.4236167.6592
2005Bikes6958251.0434231.1621
2006Bikes18901351.08178175.8399
2007Bikes24256817.5276065.992
2008Components2008052.70639.9266
2005Components574256.98650
2006Components3428213.05948.7674
2007Components5195315.2164226.0444
2008Clothing366507.8444151.1235
2005Clothing31851.162890.9593
2006Clothing455730.97294233.039
2007Clothing815853.286812489.3835
2008Accessories153299.924865.5945
2005Accessories18594.47824.293
2006Accessories86612.74631061.4872
2007Accessories275794.84034756.6546
200815496115.8941224.3038
20057582953.674326.4144
200622871907.85184419.1335
200730543780.84297538.0745
76494758.25527507.9262

ROLLUPGROUP

ROLLUPGROUP() can be used to calculate groups of subtotals. If used 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.

The following example shows only the grand total of all years and categories without the subtotal of each year with all categories:

SUMMARIZE(ResellerSales_USD
      , ROLLUP(ROLLUPGROUP( DateTime[CalendarYear], ProductCategory[ProductCategoryName]))
      , "Sales Amount (USD)", SUM(ResellerSales_USD[SalesAmount_USD])
      , "Discount Amount (USD)", SUM(ResellerSales_USD[DiscountAmount])
)

The following table shows a preview of the data as it would be received by any function expecting to receive a table:

DateTime[CalendarYear]ProductCategory[ProductCategoryName][Sales Amount (USD)][Discount Amount (USD)]
2008Bikes12968255.4236167.6592
2005Bikes6958251.0434231.1621
2006Bikes18901351.08178175.8399
2007Bikes24256817.5276065.992
2008Components2008052.70639.9266
2005Components574256.98650
2006Components3428213.05948.7674
2007Components5195315.2164226.0444
2008Clothing366507.8444151.1235
2005Clothing31851.162890.9593
2006Clothing455730.97294233.039
2007Clothing815853.286812489.3835
2008Accessories153299.924865.5945
2005Accessories18594.47824.293
2006Accessories86612.74631061.4872
2007Accessories275794.84034756.6546
76494758.25527507.9262

SUMMARIZE with ISSUBTOTAL

Enables the user to create another column, in the Summarize function, that returns True if the row contains sub-total values for the column given as argument to ISSUBTOTAL, otherwise returns False.

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

ISSUBTOTAL parameters

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

Return Value

A True value if the row contains a sub-total value for the column given as argument, otherwise returns False

Remarks

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

  • ISSUBTOTAL must be preceded by a matching name column.

Example

The following sample generates an ISSUBTOTAL() column for each of the ROLLUP() columns in the given SUMMARIZE() function call.

SUMMARIZE(ResellerSales_USD
      , ROLLUP( DateTime[CalendarYear], ProductCategory[ProductCategoryName])
      , "Sales Amount (USD)", SUM(ResellerSales_USD[SalesAmount_USD])
      , "Discount Amount (USD)", SUM(ResellerSales_USD[DiscountAmount])
      , "Is Sub Total for DateTimeCalendarYear", ISSUBTOTAL(DateTime[CalendarYear])
      , "Is Sub Total for ProductCategoryName", ISSUBTOTAL(ProductCategory[ProductCategoryName])
)

The following table shows a preview of the data as it would be received by any function expecting to receive a table:

[Is Sub Total for DateTimeCalendarYear][Is Sub Total for ProductCategoryName]DateTime[CalendarYear]ProductCategory[ProductCategoryName][Sales Amount (USD)][Discount Amount (USD)]
FALSEFALSE
FALSEFALSE2008Bikes12968255.4236167.6592
FALSEFALSE2005Bikes6958251.0434231.1621
FALSEFALSE2006Bikes18901351.08178175.8399
FALSEFALSE2007Bikes24256817.5276065.992
FALSEFALSE2008Components2008052.70639.9266
FALSEFALSE2005Components574256.98650
FALSEFALSE2006Components3428213.05948.7674
FALSEFALSE2007Components5195315.2164226.0444
FALSEFALSE2008Clothing366507.8444151.1235
FALSEFALSE2005Clothing31851.162890.9593
FALSEFALSE2006Clothing455730.97294233.039
FALSEFALSE2007Clothing815853.286812489.3835
FALSEFALSE2008Accessories153299.924865.5945
FALSEFALSE2005Accessories18594.47824.293
FALSEFALSE2006Accessories86612.74631061.4872
FALSEFALSE2007Accessories275794.84034756.6546
FALSETRUE
FALSETRUE200815496115.8941224.3038
FALSETRUE20057582953.674326.4144
FALSETRUE200622871907.85184419.1335
FALSETRUE200730543780.84297538.0745
TRUETRUE76494758.25527507.9262

Community Additions

Show:
© 2016 Microsoft