Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Using GROUPBY with dynamic filter context

Hi all,   I'm struggling with the GROUPBY() function. I would like to group my data by ITEM and doing an average on the volume. The table is filtered by a slicer on the minimum date. It seems like ...
  • AlB's avatar
    AlB
    6 years ago

    Hi Anonymous 

    In this case, you have to use the column name only, without the table name:

    SUMX( Group_Max; [Max volume] )

     

    Please mark the question solved when done and consider giving kudos if posts are helpful.

    Cheers  Datanaut

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hello AlB ,

 

Thanks for your reply. I'm trying to give it a try to your solution and I think that it could do the job but I'm struggling with the syntax.

 

How do you recall à created column in the GROUPBY function stocked in the variable?

 

VAR
Running_date= MAX(Calendar[date])
VAR
Filtered_table_to_date= FILTER(Table1; Table1[date]<=Running_date)
VAR
Group_Max=
GROUPBY(
Filtered_table_to_date;
Table1[Item];
"Max volume"; MAXX(CURRENTGROUP(); Table1[Volume]
RETURN

SUMX(Group_Max; Group_Max[Max volume])


How it should be written in the SUMX function?

 

Rgds,

 

 

AlB's avatar
AlB
Community Champion
6 years ago

Hi Anonymous 

In this case, you have to use the column name only, without the table name:

SUMX( Group_Max; [Max volume] )

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  Datanaut

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    Thank you so much, works perfectly!