Using GROUPBY with dynamic filter context

[Anonymous]

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.

[AlB]

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  

[Anonymous]

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"?

[AlB]

@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  

[Anonymous]

Ok I get what you mean now but I need a table as an output. I'm doing further calculations based on this groupby table.

[AlB]

You can create a table within a measure that will be affected by  filter context

Measure = 

Var AuxTable_ = //Your code from earlier (GROUPBY)

RETURN

// The rest of the code using that table here  

[Anonymous]

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 · Accepted solution]

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  

[Anonymous]

Thank you so much, works perfectly!