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 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'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

8 Replies

  • AlB's avatar
    AlB
    Community 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  Datanaut

    • Anonymous's avatar
      Anonymous
      Not 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"?

      • AlB's avatar
        AlB
        Community 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  Datanaut