Daily DAX : Day 378 GROUPBY

Power BI DAX: GROUPBY Function

What is GROUPBY?

The GROUPBY function in DAX is used to group rows in a table by one or more columns and apply aggregations to each group. It returns a table with the grouped results.

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

• <table>: The source table to group.
• <groupBy_columnName>: Column(s) to group by.
• <name>: Name of the new aggregated column.
• <expression>: Aggregation (e.g., SUM, AVERAGE, COUNTROWS).

How GROUPBY Works

It works similarly to SQL GROUP BY or SUMMARIZE in DAX, but is more flexible when you need to:

• Group data without removing filter context completely.
• Perform row-by-row aggregations within groups.
• Use with CURRENTGROUP() for advanced logic inside groups.

Use Cases

1. Total Sales by Region and Product Category

Sales by Region & Category = 
GROUPBY(
    Sales,
    Sales[Region],
    Sales[Category],
    "Total Sales", SUMX(CURRENTGROUP(), Sales[Amount])
)

Result: A table with Region, Category, and Total Sales for each combination.

2. Count of Customers per Country with Average Order Value

Customer Summary = 
GROUPBY(
    Orders,
    Customers[Country],
    "Customer Count", COUNTROWS(CURRENTGROUP()),
    "Avg Order Value", AVERAGEX(CURRENTGROUP(), Orders[Total])
)

Use: Create summary reports or feed into visuals.

3. Running Total Within Each Group

Running Total by Month = 
GROUPBY(
    Sales,
    Sales[YearMonth],
    "Monthly Total", SUMX(CURRENTGROUP(), Sales[Amount]),
    "Running Total", 
        SUMX(
            FILTER(
                ALLSELECTED(Sales),
                Sales[YearMonth] <= EARLIER(Sales[YearMonth])
            ),
            Sales[Amount]
        )
)

Advanced: Use CURRENTGROUP() and EARLIER for cumulative calculations.

GROUPBY vs SUMMARIZE

Feature	GROUPBY	SUMMARIZE
Preserves filter context	✅ Yes	❌ No (removes unless extended)
Use with CURRENTGROUP()	✅ Yes	❌ No
Simple aggregations	✅ Yes	✅ Yes
Performance	Slower in large models	Faster, optimized

Tip: Use SUMMARIZE for standard grouping. Use GROUPBY when you need CURRENTGROUP() or row context within groups.

Key Points

• GROUPBY returns a table, not a scalar.
• Always use SUMX, AVERAGEX, etc., with CURRENTGROUP() for aggregations.
• Best for calculated tables or complex grouped logic.
• Not ideal for measures (use SUMMARIZE or variables instead).