Forum Discussion

SDrummond_9876's avatar
SDrummond_9876
Copper Contributor
Dec 05, 2023

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.

MatchIDTeamIDDimIDAmountForAmountAgainst
11121
115112

 

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

 

Any advice is appreciated

excel
Formulas and Functions

13 Replies

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Dec 05, 2023

    Hi SDrummond_9876 

     

    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

    - Added measure [Count_Last5]
    - Revised measure [Avg_Last5_Above2]

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

    • SDrummond_9876's avatar
      SDrummond_9876
      Copper Contributor
      Dec 05, 2023
      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!!
    • SDrummond_9876's avatar
      SDrummond_9876
      Copper Contributor
      Dec 05, 2023

      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

      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        Dec 05, 2023

        SDrummond_9876 

        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 https://support.microsoft.com/en-us/office/share-onedrive-files-and-folders-9fcc2f7d-de0c-4cec-93b0-a82024800c07#:~:text=1%20Go%20to%20the%20OneDrive%20website%20and%20sign,on%20your%20link%2C%20then%20select%20Apply%20when%20you%27re, Google Drive or the like and post the shared link here

Resources