Daily DAX : Day 343 CURRENTGROUP

Power BI DAX Function: CURRENTGROUP

Description

The CURRENTGROUP function in DAX (Data Analysis Expressions) is used within the context of a calculation to reference the current group of rows in a grouped calculation, such as in a SUMMARIZE or ADDCOLUMNS function. It is primarily used in Power BI to access the rows in the current group during iterative calculations over grouped data.

Syntax

CURRENTGROUP()

Returns: A table containing the rows of the current group in the grouping operation.

Use Case

CURRENTGROUP is commonly used with the SUMMARIZE function to perform calculations on a group of rows. It allows you to reference the subset of data in the current group for further calculations, such as aggregations or filtering within the group.

Example

Suppose you have a sales table with columns Region, Product, and SalesAmount. You want to calculate the total sales for each region and then find the percentage contribution of each product within that region.

SalesByRegion = 
SUMMARIZE(
    Sales,
    Sales[Region],
    "TotalSales", SUM(Sales[SalesAmount]),
    "ProductDetails",
        ADDCOLUMNS(
            CURRENTGROUP(),
            "Product", Sales[Product],
            "SalesPercentage", 
                DIVIDE(
                    SUM(Sales[SalesAmount]),
                    CALCULATE(SUM(Sales[SalesAmount]), CURRENTGROUP())
                )
        )
)
    

Explanation of Example

• SUMMARIZE groups the Sales table by Region.
• TotalSales calculates the total sales for each region.
• ProductDetails uses ADDCOLUMNS to iterate over the rows in the current group (accessed via CURRENTGROUP).
• For each product in the group, it calculates the percentage of sales by dividing the product's sales by the total sales of the current group.

Key Points

• CURRENTGROUP can only be used within functions like SUMMARIZE or ADDCOLUMNS that define a group context.
• It does not take any arguments and dynamically references the current group's rows.
• Useful for nested calculations within grouped data, such as ratios or aggregations.

Common Scenarios

• Calculating group-level metrics (e.g., percentage contribution within a category).
• Performing row-level calculations within a group while retaining access to the group's total.
• Combining group aggregations with detailed row data in a single query.