How to Use GROUPBY in Power BI

Summarizing data is a fundamental task in any report, and Power BI's GROUPBY function gives you the power to do it with precision right inside your data model. Rather than relying just on visual filters, you can create new, aggregated tables to perform more advanced calculations. This article will walk you through exactly how GROUPBY works, why it's different from SUMMARIZE, and how to use it with practical, step-by-step examples.

Graphed

Still Building Reports Manually?

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

Watch the Demo

What Exactly is GROUPBY in Power BI?

GROUPBY is a DAX function that creates a new summary table from an existing one. Think of it like a pivot table but happening behind the scenes in your data model. It takes a detailed table (like a list of every single sale) and groups the rows based on one or more columns you specify (like "Region" or "Product Category"). Then, for each of these groups, it performs a calculation you define, such as summing up sales, counting transactions, or finding the average order value.

The output of GROUPBY is a new, aggregated physical table in your model. This new table is often much smaller and faster to work with than the original, making it a fantastic tool for optimizing performance and building complex calculations that rely on summarized values.

For example, if you have a sales table with 100,000 rows of individual transactions, you can use GROUPBY to create a table with just 5 rows - one for each sales region - showing the total sales per region.

Graphed

Still Building Reports Manually?

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

Watch the Demo

GROUPBY vs. SUMMARIZE: What’s the Difference?

If you've used DAX for a while, you may be familiar with the SUMMARIZE function. At first glance, GROUPBY and SUMMARIZE seem very similar, both functions group data and create summary tables. However, they have a key technical difference in how they perform aggregations.

• SUMMARIZE can create both grouping columns and new aggregated columns in its core syntax. It feels a bit more straightforward for simple aggregations. For example: SUMMARIZE(Sales, Sales[Region], "Total Sales", SUM(Sales[SalesAmount])).
• GROUPBY requires you to use an iterator function (like SUMX, AVERAGEX, COUNTX) along with the special CURRENTGROUP() function to perform aggregations. It doesn't allow simple aggregation functions like SUM() directly in its syntax.

So which one should you use? The general consensus in the Power BI community is that GROUPBY often offers better performance, especially in more complex scenarios. Its use of iterator functions provides more explicit control over the context of the calculation. While SUMMARIZE might feel simpler for basic tasks, getting comfortable with GROUPBY is a great skill that pays off as your DAX formulas become more advanced.

Breaking Down the GROUPBY Syntax

Before we jump into examples, let's look at the structure of the GROUPBY function. Understanding its components is the key to using it effectively.

GROUPBY(
  <table>,
  <groupBy_columnName1>,
  [<groupBy_columnName2>, ...],
  <name>,
  <expression>
)

Here's what each part does:

• <table>: The source table you want to summarize (e.g., 'Sales').
• <groupBy_columnName>: The column (or columns) you want to use for grouping the data (e.g., 'Sales'[Region]). You can list multiple columns to group by more than one level.
• <name>: The name you want to give your new calculated column, enclosed in double quotes (e.g., "Total Region Sales").
• <expression>: The calculation to perform for each group. This expression must use an iterator function and the CURRENTGROUP() function. For example, SUMX(CURRENTGROUP(), 'Sales'[SalesAmount]).

The Secret Sauce: CURRENTGROUP()

CURRENTGROUP() is what makes GROUPBY so powerful. For each group the function creates (e.g., for all the 'North America' sales), CURRENTGROUP() temporarily holds all the original table rows belonging to just that group. An iterator function like SUMX can then run its calculation on that temporary subset of data. This happens sequentially for every group until the final summary table is complete.

How to Use GROUPBY: A Step-by-Step Example

Let's create a summary table from scratch. Imagine we have a 'Sales' table with the following columns: Date, Region, ProductCategory, SalesAmount, and UnitsSold.

Our Goal: Create a new table that shows the total sales for each region.

Step 1: Create a New Table

In Power BI Desktop, navigate to the Modeling tab in the ribbon and click on New Table.

Step 2: Enter the GROUPBY Formula

In the formula bar that appears, type the following DAX expression:

Sales by Region = 
GROUPBY(
    'Sales',
    'Sales'[Region],
    "Total Sales",
    SUMX(CURRENTGROUP(), 'Sales'[SalesAmount])
)

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 3: Analyze the Formula

• GROUPBY('Sales', 'Sales'[Region], ...: We're telling Power BI to take the 'Sales' table and group it by the 'Region' column.
• "Total Sales", ...: This creates a new column in our output table named "Total Sales."
• SUMX(CURRENTGROUP(), 'Sales'[SalesAmount]): This is the calculation. For each region group, SUMX takes the temporary table provided by CURRENTGROUP() and sums up the values in the 'SalesAmount' column for only the rows in that group.

Step 4: View the Result

After you press Enter, a new table named "Sales by Region" will appear in your Data model view. If you click on it, you will see a simple, clean summary table showing each unique region and its corresponding total sales.

Going Further: Advanced GROUPBY Examples

Once you've mastered the basics, you can expand on the formula to handle more complex scenarios.

Example 1: Grouping by Multiple Columns

What if you need to see sales by both region and product category? Simply add another column to the grouping section.

Sales by Region and Category = 
GROUPBY(
    'Sales',
    'Sales'[Region],
    'Sales'[ProductCategory],
    "Total Sales",
    SUMX(CURRENTGROUP(), 'Sales'[SalesAmount])
)

This will create a table with unique combinations of Region and ProductCategory, preventing you from having to create multiple, separate summary tables.

Example 2: Creating Multiple Aggregations

Need to calculate total sales and count the number of transactions at the same time? Just add another name–expression pair to the formula.

Region Sales and Transaction Count = 
GROUPBY(
    'Sales',
    'Sales'[Region],
    "Total Sales",
    SUMX(CURRENTGROUP(), 'Sales'[SalesAmount]),
    "Transaction Count",
    COUNTROWS(CURRENTGROUP())
)

Here, we used COUNTROWS(CURRENTGROUP()) to count the number of rows within each region's group, effectively giving us the total number of transactions.

Example 3: Finding an Average

To calculate the average units sold per transaction for each region, you'd use the AVERAGEX iterator function.

Regional Unit Averages = 
GROUPBY(
    'Sales',
    'Sales'[Region],
    "Average Units Sold",
    AVERAGEX(CURRENTGROUP(), 'Sales'[UnitsSold])
)

Graphed

Still Building Reports Manually?

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

Watch the Demo

Common Mistakes to Avoid

As you start using GROUPBY, you might run into a couple of common stumbling blocks. Here's what to watch out for:

1. Forgetting to use an Iterator Function: A frequent mistake is trying to use a simple aggregation like SUM('Sales'[SalesAmount]) instead of SUMX(CURRENTGROUP(), 'Sales'[SalesAmount]). GROUPBY strictly requires an iterator function paired with CURRENTGROUP() to perform its calculations. The formula will return an error without it.
2. Confusing Physical Tables with Visual Grouping: Remember, the output of GROUPBY is a new physical table in your Power BI data model. This is different from the on-the-fly grouping that happens when you drag fields into a Table or Matrix visual. Use GROUPBY when you need a summarized table for use in other complex measures or DAX calculations, not just for a one-off visual.

A Quick Best Practice

When you start writing more complex DAX measures that need an interim summarized table, it's a great practice to store your GROUPBY table in a variable. This makes your code much cleaner and easier to read and debug.

Advanced Sales Metric = 
VAR SummaryTable = 
    GROUPBY(
        'Sales',
        'Sales'[Region],
        "Total Sales",
        SUMX(CURRENTGROUP(), [SalesAmount])
    )
RETURN
    AVERAGEX(SummaryTable, [Total Sales])

In this example, we create a summary table of sales by region and then calculate the average of those regional totals.

Final Thoughts

The GROUPBY function in DAX is an incredibly useful tool for creating new, aggregated tables directly within your data model. By understanding its syntax and the essential role of CURRENTGROUP() paired with iterator functions, you unlock a more efficient way to handle complex summaries and optimize your reports for better performance.

For those short on time, learning the nuances of DAX functions like GROUPBY can be a steep climb. Hours can be spent writing, debugging, and perfecting formulas to get your reports just right. We created Graphed because we believe getting insights shouldn't require you to become a data analyst. You can simply connect data sources like Google Analytics or your CRM, and ask for what you need in plain English - like "Show me total sales grouped by region and product category." Graphed builds the report for you in seconds, saving you from the complexities of DAX and instead giving you immediate answers.