Forum Discussion

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

Silver Contributor

Dec 06, 2023

Hi SDrummond_9876

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)

SDrummond_9876

Copper Contributor

Dec 07, 2023

Lorenzo

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.

Stats.xlsx640 KB

Lorenzo
Silver Contributor
Dec 07, 2023
SDrummond_9876

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
- SDrummond_9876
  Copper Contributor
  Dec 07, 2023
  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
  - Lorenzo
    Silver Contributor
    Dec 07, 2023
    SDrummond_9876
    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???
Lorenzo
Silver Contributor
Dec 07, 2023
SDrummond_9876

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