How to use GROUPBY function in PowerBI?

Question

I tried using group by DAX function in PowerBI as Measure, New Column, New Table but I get an error in validating the function,

New Table = GROUPBY(
            'tab1',
            'tab1'[color],
            "Group By color",
            sum('tab1'[count_lables])
          )

Error : Function 'GROUPBY' scalar expressions have to be Aggregation functions over CurrentGroup(). The expression of each Aggregation has to be either a constant or directly reference the columns in CurrentGroup().

Solution

The error says you need to use an aggregation function over a group, you are using SUM function which doesn't sum values over any group. In your case you need to use SUMX aggregation function with CURRENTGROUP() option. The CURRENTGROUP determines the groups that must be added based on the current row.

Try using the below expression instead.

New Table = GROUPBY (
        tab1,
        tab1[color],
        "Group by color", SUMX ( CURRENTGROUP (), tab1[count lables] )
    )

Let me know if this helps.

Comments

[Dave D.]

Thanks @alejandro zuleta it works for me now, do you know material that can be referred for usage of GROUPBY AND SUMMERIZE in Power BI?

[alejandro zuleta]

@DaveD., you are welcome. Sure, check this great article by Marco Russo.