# Getting The Average For the Top 5 Results

Copper Contributor

# 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 for each dimension e.g.

So for example, below entry is into the MatchStats table shows the entries for goals scored (DimID 1) per each team involved in the game.

 MatchID TeamID DimID AmountFor AmountAgainst 1 1 1 2 1 1 15 1 1 2

Using PowerPivot I've already created a number of measures for total goals, avg goals etc. What I want to do now is calculate

(A) The average number of games where over 2 goals have been scored (AmountFor + AmountAgainst)

(B) The average number of games in the last 5 matches where over 2 goals have been scored

I can calculate (a) using a bit of a messy method:

=CALCULATE(COUNT(Matches[MatchID]), FILTER(MatchStats, CALCULATE([Sum of AmountFor] + [Sum of AmountAgainst], 'Dimensions'[DimName] = "GoalsScored") >2.5)) / CALCULATE(COUNT(Matches[MatchID]))

But I was hoping that I could do this with AVERAGEX but to no avail. Before I tackle (B) I want to get (A) working with best practices as incorporating TOPN function into the above would look to be tricky

13 Replies

# Re: Getting The Average For the Top 5 Results

Not sure I understand the difference between [MatchID] and [DimID] but you shared very few information + we don't know what's the purpose of the 'Dimensions' table and if there's any relationship set between 'MatchStats' & 'Dimensions'. All this to say that what's attached might be partially/totally wrong (pic. cropped)

EDIT

- Revised measure [Avg_Last5_Above2]

Otherwise if < 5 Matches in 'MatchStats' [Avg_Last5_Above2] will be wrong

# Re: Getting The Average For the Top 5 Results

Sorry @Lorenzo Sorry Lz, I couldn't see where to attach the sample file, it includes the data model

I tried to drag and drop but I get a message saying .xlsx is not supported

I don't see any option to attach either

# Re: Getting The Average For the Top 5 Results

Also, I'm showing how much of a newbie I am, I didn't even know you could use the created measures as you've done in your sample.
Without being able to attach the file, let me give you some more info
The Dimensions table (bad name I know) is a list of different type of attributes:
1. Goals
2. Offsides
3. Corners

There are 2 measure tables, (again bad names)
Matches (MatchID, HomeTeamID, AwayTeamID, MatchDate)
MatchStats (as per sample above)

Let me review your samples, already they look much better than mine!!

# Re: Getting The Average For the Top 5 Results

I couldn't see where to attach the sample file, it includes the data model

You might not be autho. to attach a file yet. In the meantime upload & share your file with OneDrive, Google Drive or the like and post the shared link here

# Re: Getting The Average For the Top 5 Results

Apologies for the delay, having trouble getting the file loaded in onedrive due to restrictions with work account. In any case, I've been trying to apply your formulas in mine.
Can you correct my assumption but the method by which you're using COUNTROWS will only return the top 5 matchid's for the whole table.
I need to work out that on a team by team basis so I used something like this:
=CALCULATE(DISTINCTCOUNT([MatchID]),
FILTER(
TOPN(
5
,SUMMARIZE(MatchStats, MatchStats[MatchID], MatchStats[TeamID]
,"Goals"
,[TotalGoalsScored]
)
, MatchStats[MatchID],DESC
)
, [Goals]> 2
)
)

But its not returning the right number and not returning values for some teams.

I know this would be easier to understand with the sample file

FYI - TotalGoalsScored definition:

CALCULATE(SUM([AmountFor]),'Dimensions'[DimName]="GoalsScored")

# Re: Getting The Average For the Top 5 Results

I understand and appreciate security at work (there's almost always a way around, i.e. Internet via Mobile phone) though. Afraid to say this won't work for me - Sorry. A number of things remain unclear to me re. your setup and I'm far from being a PowerPivot/DAX expert so without your sample workbook no chance at all I can do something but waste your and my time

Re. Can you correct my assumption but the method by which you're using COUNTROWS will only return the top 5 matchid's for the whole table
Correct. That was my understanding of your expectation. Obviously I missed the point :(

BTW, on the Toolbar (when you post/reply) there's a way to post a structured code/formula sample:

(hope this help)

# Re: Getting The Average For the Top 5 Results

Appreciate the reply, trying to diagnose the issue without the actual data / data model in front of you is a tough ask. In any case, it looks like I got the authority to attach now. If you have any more time to review it would be greatly appreciated.

FYI - I'm making slow inroads into figuring it out myself, I wasn't aware that you could create a table based on the DAX code i.e. EVALUATE...SUMMARIZE so I'm seeing how the data looks when it comes time to count. The main issue is trying to create that grouping by MatchID, TeamID and the totals in their last 5 games.

# Re: Getting The Average For the Top 5 Results

Thanks for sharing your wbook (haven't looked at it yet). I don't promisse anything given my level of expertise but will have a look at it

Re. I'm making slow inroads into figuring it out myself, I wasn't aware that you could create a table based on the DAX code i.e. EVALUATE

In case you don't know it there's a amazing Excel (and PowerBI) add-in called 'DAX Studio' avail. on sqlbi/Tools. This site is really a ref. IMHO

# Re: Getting The Average For the Top 5 Results

Assuming you didn't change the data you shared and "manually" calc., what's the Avg goals of the last 5 matches for say?:

- Arsenal

- Newcastle

# Re: Getting The Average For the Top 5 Results

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

# Re: Getting The Average For the Top 5 Results

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

# Re: Getting The Average For the Top 5 Results

I can possibly reconcile Newcastle (6) but not Arsenal:

(I get exactly the same thing if I do it with Excel) So I wonder if we look at the same thing???

# Re: Getting The Average For the Top 5 Results

Going back to my previous post, I think we need to break it down into smaller parts (so I understand it a bit better). I've added another sheet (Summarized) just to highlight what I think I need to do as per my previous post.

Perhaps there is a better way of doing this in DAX but I think we need to add a column to the Summarized table with a row number value indicating what the match number per team. So, say we wanted to calculate the average of the last 2 games per team, then the matches for Arsenal and Aston Villa highlighted below are the ones that would be included in the calculation