Downloads
DAXTable constructionAdvanced

GROUPBY

Groups a table by columns and calculates totals for each group, using CURRENTGROUP() to reach the rows in each group.

Syntax
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

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

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.

Related functions

All DAX functions · View as Markdown · DAX fundamentals