GROUPBY DAX Function (Table manipulation)
Creates a summary the input table grouped by the specified columns.
Syntax
| Parameter | Attributes | Description |
|---|---|---|
| Table |
The input table. |
|
| GroupBy_ColumnName | Optional Repeatable |
A column to group by. |
| Name | Optional Repeatable |
A column name to be added. |
| Expression | Optional Repeatable |
The expression of the new column. |
Return values
A table with the selected columns for the GroupBy_columnName arguments and the grouped by columns designated by the name arguments.
Remarks
Most of the times, SUMMARIZE can be used instead of GROUPBY.
GROUPBY is required to aggregate the result of a column computed in a previous table expression.
» 1 related function
Examples
-- GROUPBY is useful to group by columns with no lineage
-- Each column added by GROUPBY must iterate CURRENTGROUP().
-- Moreover, you cannot use CALCULATE inside
-- a GROUPBY iteration.
DEFINE
VAR AverageCustomerSales =
AVERAGEX ( Customer, [Sales Amount] )
VAR TaggedCustomers =
SUMMARIZECOLUMNS (
Customer[CustomerKey],
"Customer Category",
IF ( [Sales Amount] >= AverageCustomerSales, "Above Average", "Below Average" )
)
VAR Result =
GROUPBY (
TaggedCustomers,
[Customer Category],
"# Customers", COUNTX ( CURRENTGROUP (), 1 )
)
EVALUATE
Result
| Customer Category | # Customers |
|---|---|
| Below Average | 18,062 |
| Above Average | 807 |
DEFINE
VAR CustomersAndCategories =
SUMMARIZE ( Sales, Customer[CustomerKey], 'Product'[Category] )
VAR CustomersWithNumCategories =
GROUPBY (
CustomersAndCategories,
'Product'[Category],
"@Customers", SUMX ( CURRENTGROUP(), 1 )
)
EVALUATE
CustomersWithNumCategories
ORDER BY
'Product'[Category]
| Category | @Customers |
|---|---|
| Audio | 997 |
| Cameras and camcorders | 1,873 |
| Cell phones | 552 |
| Computers | 2,088 |
| Games and Toys | 5,785 |
| Home Appliances | 1,946 |
| Music, Movies and Audio Books | 377 |
| TV and Video | 3,421 |
EVALUATE
VAR RoundedSales =
SELECTCOLUMNS (
VALUES ( 'Product'[Product Name] ),
"First Letter", LEFT ( 'Product'[Product Name], 1 ),
"Rounded Sales", MROUND ( [Sales Amount], 10000 )
)
VAR GroupByFirstLetter =
GROUPBY (
RoundedSales,
[First Letter],
"Sum Rounded Sales", SUMX ( CURRENTGROUP (), [Rounded Sales] )
)
RETURN
GroupByFirstLetter
ORDER BY [First Letter]
| First Letter | Sum Rounded Sales |
|---|---|
| A | 6,080,000 |
| C | 7,050,000 |
| F | 5,520,000 |
| H | 30,000 |
| L | 3,160,000 |
| M | 160,000 |
| N | 1,000,000 |
| P | 2,460,000 |
| R | 0 |
| S | 1,270,000 |
| T | 1,060,000 |
| W | 1,860,000 |
Related articles
Learn more about GROUPBY in the following articles:
-
Nested grouping using GROUPBY vs SUMMARIZE
DAX introduced a GROUPBY function that should replace SUMMARIZE in some scenarios. This article describes how to use GROUPBY in nested grouping scenarios and other improvements. » Read more
-
Preparing a data model for Sankey Charts in Power BI
This article describes how to correctly shape a data model and prepare data to use a Sankey Chart as a funnel, considering events related to a customer (contact, trial, subscription, renewal, and others). » Read more
-
Differences between GROUPBY and SUMMARIZE
Both GROUPBY and SUMMARIZE are useful functions to group by columns. However, they differ in both performance and functionalities. Knowing the details lets developers choose the right function for their specific scenario. » Read more
Related functions
Other related functions are:
Last update: Nov 14, 2024 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo
Microsoft documentation: https://docs.microsoft.com/en-us/dax/groupby-function-dax