GROUPBY – aggregations in data model using DAX (DAX – Power Pivot, Power BI)
This article is about the GROUPBY function. It creates groups or subtotals in DAX (works similarly to Pivot Tables).
We will use this table with cars, that can be grouped by various columns.
Simple grouping
Let´s group the table by Brands ( = create a list of brands).
Create a new calculated table and define it by this command:
- List of brands = GROUPBY(
cars,
cars[Brand])
The syntax contains:
- Name of table
- Name of column used for grouping.
Grouping by multiple columns
But what if you needed to group the table not only by Brand, but also by Models?
You can add new argument referencing the other column:
- List of brands and models = GROUPBY(
cars,
cars[Brand],
cars[Model])
Now the syntax contains:
- Name of table
- All columns used for grouping.
Grouping with calculations
Off course, we can add some sums, counts or something like this. In this example I´d like to see the sums and counts:
- List of brands and models and prices = GROUPBY(
cars,
cars[Brand],
cars[Model],
“Total price”,
SUMX(
CURRENTGROUP(),
cars[ Price ]))
Now the syntax contains:
- Name of table
- All columns used for grouping
- Name of first calculated column
- Calculation, using such functions as COUNTX, SUMX… combined with CURRENTGROUP
- Name and calculation for other column etc.
Two additional notes
- There is a SUMMARIZE function, quite similar to GROUPBY
- GROUPBY and SUMMARIZE can be simply used to get the number of unique values, based on multiple columns.
Hi. Good Article. I’d like to group by a table like that. But I need to be able to filter the information after group by per date. Is there any way to do that?
Saved my day. Thanks.
Is there a way to group by price range?
Range #Cars
$0 – $19,999
$20,000 – $29,999
$30,000 – $49,999
$50,000 +
Charles, after groupby table created, then develop measures to price ranges.