Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
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"?
@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
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
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,
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
43 | |
19 | |
18 |