Forum Discussion
Getting The Average For the Top 5 Results
I have a small football stats spreadsheet where I'm trying to calculate certain measures per team, dimensions (goals, corners etc), match etc. Each entry into the MatchStats table will have an entry ...
In the sample I sent, I work out
- Arsenal 14 /5 = 2.8
- Newcastle only have 1 game, but applying the same calc - 6/5 = 1.2
- Arsenal 14 /5 = 2.8
- Newcastle only have 1 game, but applying the same calc - 6/5 = 1.2
SUMMARIZE(MatchStats, MatchStats[MatchID], MatchStats[TeamID], "Goals", [TotalGoalsScored])I'm still trying to break this down in a step by step fashion so first, create a summarized view of the table by match id and team id as per this code.
Next, I think an additional column to be added to this with the row number of each entry per match, per team (don't know how to do this, trying to add a rank value per row). This should be created on a descending order i.e. 1 would be the latest match for the team, 2 the previous match for that team and so on
From that, filter out the rows the top 5 rows per each team
And lastly count the number of rows where they're filtered by goals > 2