GROUPBY
Groups a table by columns and calculates totals for each group, using CURRENTGROUP() to reach the rows in each group.
GROUPBY(<table>, <groupBy_columnName> [, <groupBy_columnName>] ..., "<name>", <aggregation expression using CURRENTGROUP()> [, "<name>", ...])Returns: A table.
What it does
GROUPBY groups rows like SUMMARIZE, but each total is built from CURRENTGROUP(), the set of rows in the group. Use it for totals that are plain row arithmetic over each group, such as the sum of quantity times price. For most reports, SUMMARIZECOLUMNS and measures are simpler and keep one definition across the model, so GROUPBY is seldom the first choice.
Syntax and arguments
- table: the table to group. Usually a fact table that holds the group columns.
- groupBy_columnName: one or more columns that define each group.
- name and expression: a new column. The expression must work on
CURRENTGROUP(), usually inside an iterator such asSUMX.
Example
Units and revenue for each product key, summed from the sales lines in each group, without a measure.
Product Totals =
GROUPBY (
Sales,
Sales[ProductKey],
"Units", SUMX ( CURRENTGROUP (), Sales[SalesQuantity] ),
"Revenue", SUMX ( CURRENTGROUP (), Sales[SalesAmount] )
)
Each row is a product key with the units and revenue of its sales lines. Totals are built from the raw lines in the group, not from the measures in the model.
Example: a second use
Number of lines and the largest single line for each store.
Store Line Stats =
GROUPBY (
Sales,
Sales[StoreKey],
"Lines", COUNTX ( CURRENTGROUP (), Sales[SalesAmount] ),
"Largest Line", MAXX ( CURRENTGROUP (), Sales[SalesAmount] )
)
COUNTX counts the lines in the group that have an amount. MAXX finds the largest amount. This is useful for a quick profile of a raw data pull.
Things to watch
- An aggregation must use
CURRENTGROUP()inside an iterator. A plain column reference on its own does not work. - For columns from other tables,
SUMMARIZEorSUMMARIZECOLUMNSis the usual route. - Totals are only as good as the expression you write. They can drift from a measure with the same name in the model.
- Each group is calculated by looping over its rows, so test the speed on the real data size.
In business terms
Quick custom totals on a raw extract, such as spend and count by vendor or by account in a data pull, when a full model is not needed. For ongoing reports, measures keep one definition for every page.