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 ...
excel
Formulas and Functions
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

      • SDrummond_9876's avatar
        SDrummond_9876
        Copper Contributor
        Dec 06, 2023

        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")

Resources