How to Group By in Power BI DAX

If you're coming to Power BI from a SQL background, you might find yourself looking for a simple GROUP BY statement to summarize your data. While there isn't a direct one-to-one equivalent, Power BI’s DAX language provides powerful functions that accomplish the same goal and more. This article will show you how to use DAX functions like SUMMARIZE and GROUPBY to aggregate your data tables effectively.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why Is Grouping Data Important?

Most raw datasets are too detailed for high-level analysis. You might have thousands or even millions of rows representing individual sales, website sessions, or customer transactions. To find meaningful patterns, you need to consolidate this data. Grouping allows you to:

• Summarize Information: Condense large volumes of data into a manageable summary. For example, instead of looking at 100,000 individual sales, you can view total sales for each of your 10 product categories.
• Calculate Subtotals: Quickly calculate key metrics for different segments. You might want to see the total revenue per region, average order value per marketing channel, or customer count per city.
• Prepare Data for Visuals: Create concise, aggregated tables that are easier to use in charts and graphs, improving dashboard performance and clarity.

In short, grouping transforms your granular data into structured information, which is the first step toward uncovering actionable insights.

The Main DAX Functions for Grouping Data

While Power BI's report visuals automatically group data when you drag in a category and a measure, there are times you need to create a physical summary table in your data model. This is where DAX table functions come in. The two primary functions for this are SUMMARIZE and GROUPBY.

1. The SUMMARIZE Function

SUMMARIZE is the most common and versatile function for creating summary tables in DAX. It generates a table that groups your data by the columns you specify and then adds columns with aggregated calculations.

The basic syntax looks like this:

SUMMARIZE( <Table>, <GroupBy_ColumnName1>, [<GroupBy_ColumnName2>], ..., "<NewColumnName>", <Expression> )

• <Table>: The table you want to summarize (e.g., 'Sales').
• <GroupBy_ColumnName>: The column(s) you want to group your data by (e.g., 'Sales'[Region]).
• "<NewColumnName>": The name you give to your new calculated column (in quotes).
• <Expression>: The DAX calculation you perform (e.g., SUM('Sales'[Sale Amount])).

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

2. The GROUPBY Function

The GROUPBY function works similarly but is designed to perform iterations over the grouped data. It’s particularly useful when you need to use iterator functions like SUMX, AVERAGEX, or MINX within your aggregation. It uses a special function called CURRENTGROUP() to perform these row-by-row calculations on the grouped subset of data.

The syntax for GROUPBY is slightly different:

GROUPBY( <Table>, <GroupBy_ColumnName1>, [<GroupBy_ColumnName2>], ..., "<NewColumnName>", <Expression> )

The main difference is in the expression, which typically involves an iterator function and CURRENTGROUP(). We’ll look at a practical example later on.

Step-by-Step Guide: Grouping with SUMMARIZE

Let's walk through a practical example. Imagine we have a 'Sales' table with the following columns: [Date], [Region], [Product Category], [Sale Amount], and [Units Sold].

Our goal is to create a new summary table showing total sales and units sold for each product category within each region.

Step 1: Create a New Table in Power BI

The output of SUMMARIZE is a completely new table in your data model. To create one, go to the Modeling tab in Power BI Desktop and click on New Table.

This will open the DAX formula bar, where you'll write your expression.

Step 2: Start with a Simple Grouping

Let's start by grouping our sales data by a single column: [Product Category]. We want to see the total sales for each category.

In the formula bar, type the following DAX expression:

SalesByCategory = SUMMARIZE( 'Sales', 'Sales'[Product Category], "Total Sales", SUM('Sales'[Sale Amount]) )

Let's break that down:

• SalesByCategory = This is the name we are giving our new table.
• SUMMARIZE('Sales', ...): We are telling DAX to summarize our 'Sales' table.
• 'Sales'[Product Category], ...: This is the column we are grouping by.
• "Total Sales", SUM('Sales'[Sale Amount]): We are creating a new column named "Total Sales" and defining its value as the sum of the 'Sale Amount' for each product category.

Once you press Enter, Power BI will generate a new table with two columns: Product Category and Total Sales.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 3: Group by Multiple Columns

Now, let's extend our example to group by both [Product Category] and [Region] to get a more detailed view. We simply add the second grouping column to our formula:

SalesByCategoryAndRegion = SUMMARIZE( 'Sales', 'Sales'[Product Category], 'Sales'[Region], "Total Sales", SUM('Sales'[Sale Amount]) )

This new table will show each unique combination of category and region, along with the total sales for that specific combination.

Step 4: Add Multiple Aggregations

What if you want to see both total sales and the total number of units sold? You can add as many aggregation "name" and "expression" pairs as you need.

Modify the formula to include a calculation for [Units Sold]:

SalesSummary = SUMMARIZE( 'Sales', 'Sales'[Product Category], 'Sales'[Region], "Total Sales", SUM('Sales'[Sale Amount]), "Total Units", SUM('Sales'[Units Sold]) )

Your resulting 'SalesSummary' table will now have four columns: Product Category, Region, Total Sales, and Total Units, giving you a robust overview that you can use for other calculations or visuals.

Using the GROUPBY Function for More Complex Scenarios

So when would you use GROUPBY instead of SUMMARIZE? The main reason is when you need to perform an aggregation that requires iterating over the data row by row after the grouping has occurred.

Let's imagine our 'Sales' table doesn't have a [Sale Amount] column. Instead, it has [Unit Price] and [Units Sold], and we want to calculate revenue within each group. The formula is [Unit Price] * [Units Sold]. We have to do this multiplication for every single row before summing it up for the group.

This is a perfect job for an iterator function like SUMX paired with GROUPBY.

RevenueByCategory = 
GROUPBY(
    'Sales',
    'Sales'[Product Category],
    "Total Revenue", 
    SUMX(CURRENTGROUP(), 'Sales'[Unit Price] * 'Sales'[Units Sold])
)

Here's what’s happening:

• The GROUPBY function groups the 'Sales' table by [Product Category] as before.
• The key difference is the expression: SUMX(CURRENTGROUP(), 'Sales'[Unit Price] * 'Sales'[Units Sold]).
• CURRENTGROUP(): This magical DAX function represents the subset of rows belonging to the current group being calculated (e.g., all rows where Product Category is "Electronics").
• SUMX(...): Iterates through each row of that CURRENTGROUP(), calculates 'Sales'[Unit Price] * 'Sales'[Units Sold], and then sums up the results.

Important Considerations and Best Practices

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Measures vs. Calculated Tables

For most reporting, you don't actually need to create a physical summary table. Power BI is designed to handle this implicitly using Measures. For example, instead of creating the 'SalesByCategory' table, you could simply:

• Create a measure: Total Sales Measure = SUM('Sales'[Sale Amount])
• Drag the [Product Category] column and your new [Total Sales Measure] into a table or bar chart visual.

Power BI’s visual engine will automatically perform the grouping on the fly. This is more flexible and memory-efficient. You should only create physical grouped tables with SUMMARIZE or GROUPBY when you need that summarized data as a building block for further modeling, more complex measures, or to improve performance on extremely large datasets.

Keep Your Model Clean

Be sure to give your new summary tables and their columns descriptive names. A table named 'SummaryV2' isn't helpful to you or your colleagues six months from now. Use clear names like 'RegionalSalesSummary' or 'MarketingChannelPerformance'.

Choose the Right Tool for the Job

• For Visuals: Use Measures and let Power BI's visuals handle grouping implicitly. It's dynamic and efficient.
• For Simple Summary Tables: Use SUMMARIZE when you need a physical, static summary of your data.
• For Complex Iterations: Use GROUPBY and CURRENTGROUP() when your aggregation formula needs to perform row-level calculations within each group (e.g., using SUMX).

Final Thoughts

Learning to group and summarize data is a fundamental skill in Power BI. While there's no single GROUP BY button, DAX provides powerful functions like SUMMARIZE and GROUPBY that offer far more flexibility. By mastering these functions, you can shape your data model, perform complex calculations, and prepare your data for clear and insightful visualizations.

Of course, mastering DAX takes time and practice. If you need to answer business questions quickly without writing formulas, new tools can translate your questions directly into insights. At our company, we designed a solution for exactly that. With Graphed, you connect your data and simply ask things like, "What were our total sales by region and product category last quarter?" It automatically builds the charts and dashboards for you, turning hours of data wrangling and formula writing into a simple conversation.