Forum Discussion
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
- Added measure [Count_Last5]
- Revised measure [Avg_Last5_Above2]
Otherwise if < 5 Matches in 'MatchStats' [Avg_Last5_Above2] will be wrong
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
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
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 fileFYI - TotalGoalsScored definition:
CALCULATE(SUM([AmountFor]),'Dimensions'[DimName]="GoalsScored")
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)