A column to group by or a call to ROLLUPGROUP function to specify a list of columns to group by with subtotals.
Name
Optional, Repeatable
A column name to be added.
Expression
Optional, Repeatable
The expression of the new column.
Return Values
A table with the selected columns for the groupBy_columnName arguments and the summarized columns designed by the name arguments.
Remarks
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.
groupBy_columnName must be either in table or in a related table to table.
Each name must be enclosed in double quotation marks.
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.
This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
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.
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)]
2008
Bikes
12968255.42
36167.6592
2005
Bikes
6958251.043
4231.1621
2006
Bikes
18901351.08
178175.8399
2007
Bikes
24256817.5
276065.992
2008
Components
2008052.706
39.9266
2005
Components
574256.9865
0
2006
Components
3428213.05
948.7674
2007
Components
5195315.216
4226.0444
2008
Clothing
366507.844
4151.1235
2005
Clothing
31851.1628
90.9593
2006
Clothing
455730.9729
4233.039
2007
Clothing
815853.2868
12489.3835
2008
Accessories
153299.924
865.5945
2005
Accessories
18594.4782
4.293
2006
Accessories
86612.7463
1061.4872
2007
Accessories
275794.8403
4756.6546
With ROLLUP
The addition of the ROLLUP syntax modifies the behavior of the SUMMARIZE function by adding rollup rows to the result on the groupBy_columnName columns. ROLLUP can only be used within a SUMMARIZE expression.
Example
The following example adds rollup rows to the Group-By columns of the SUMMARIZE function call:
With ISSUBTOTAL, you can create another column in the SUMMARIZE expression that returns True if the row contains subtotal values for the column given as argument to ISSUBTOTAL, otherwise returns False. ISSUBTOTAL can only be used within a SUMMARIZE expression.
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])
)