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

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
6 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    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's avatar
      AlB
      Community Champion
      6 years ago

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