<!-- https://learnsql.cpetoday.com/functions/dax/groupby -->
# GROUPBY

Language: DAX
Category: Table construction
Syntax: GROUPBY(<table>, <groupBy_columnName> [, <groupBy_columnName>] ..., "<name>", <aggregation expression using CURRENTGROUP()> [, "<name>", ...])
Summary: Groups a table by columns and calculates totals for each group, using CURRENTGROUP() to reach the rows in each group.
Returns: A table.
Level: Advanced
Related: SUMMARIZE, SUMMARIZECOLUMNS, ADDCOLUMNS, VALUES, CALCULATE

## 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 as `SUMX`.

## Example
Units and revenue for each product key, summed from the sales lines in each group, without a measure.

```dax
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.

```dax
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, `SUMMARIZE` or `SUMMARIZECOLUMNS` is 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.
