Forum Discussion
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 the GROUPBY function is not considering the filter context as the average volume is the same with or without the date slicer.
Table:
ITEM | DATE | VOLUME
A; 01/01/2013; 50
A; 05/01/2013; 60
A; 05/01/2015; 70
B; 01/01/2013; 50
B; 06/02/2013; 50
B; 05/03/2015; 40
Here is the code I'm using.
=GROUPBY(
Table;
Table[ITEM];
"Average volume by item";
AVERAGEX(
CURRENTGROUP();
Table[Volume]
)
)
I would like the GROUPBY() to group the table already filtered with the date slicer, for example Date > 01/01/2017.
I should end up with this:
ITEM | AVG VOLUME
A; 70
B; 40
It's not the case and it seems like GROUPBY is ignoring the filter context.
What would be the right way to do it?
Thank you very much.
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

8 Replies
- AlBCommunity Champion
Hi Anonymous
You seem to be creating a calculated table. That is static and will not be affected by filter context at all. Try creating a table visual instead, with the appropriate measure
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers

- AnonymousNot applicable
Hi AIB, thanks for you help.
Is their a function I could use in this measure instead of GROUPBY to average on the same "ITEM"?
- AlBCommunity Champion
Anonymous
How about you just
1. Place Table[ITEM] in the rows of a matrix visual
2. Place this simple measure in the visual
Measure = AVERAGE( Table[Volume] )
3. Use a slicer for the date
Wouldn't that yield what you're looking far or am I completely misunderstanding?
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
