SUMMARIZE
Returns one row for each combination of the grouping columns that exists in the data.
SUMMARIZE(<table>, <groupBy_columnName> [, <groupBy_columnName>] ...)Returns: A table.
What it does
SUMMARIZE groups a table by the columns you name and returns a row for each combination that appears in the data. A combination with no rows does not appear. Use it to get a list of groups, such as each product category and year that had sales. To add totals to that list, use ADDCOLUMNS, or use SUMMARIZECOLUMNS. Adding totals inside SUMMARIZE is an older pattern that is no longer the recommended way.
Syntax and arguments
- table: the table to group, often a fact table such as Sales. Columns from related dimension tables can be used when those tables are related.
- groupBy_columnName: one or more columns to group by. Each distinct combination becomes one row.
Example
A list of product category and year combinations that had sales.
Category Year Groups =
SUMMARIZE (
Sales,
Product[ProductCategory],
'Date'[Year]
)
The result has one row for each category and year pair that has at least one sales line. Pairs with no sales do not appear.
Example: a second use
A list of store and channel pairs with their sales, used as the base for a table of totals.
Store Channel Pairs =
ADDCOLUMNS (
SUMMARIZE ( Sales, Stores[StoreName], Channel[ChannelName] ),
"Sales", [Total Sales]
)
SUMMARIZE builds the list of pairs, and ADDCOLUMNS adds the sales for each pair. This is the pattern the function is meant for: group first, then add totals.
Things to watch
- SUMMARIZE returns only combinations that exist in the data. For a full grid that includes empty pairs, use
CROSSJOINor a list of the dimension values. - Do not add measures inside SUMMARIZE. Use
ADDCOLUMNSfor totals. - Grouping by a column with many values gives a large table.
- Columns from another table work only through a relationship.
- Duplicate combinations collapse into one row, so the count of rows is the count of distinct combinations.
In business terms
Group lists for management packs: each entity and period with activity, each cost center that booked expense, and each product line that sold. Often it is the base table before totals are added.