Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Pan_Forex
Helper III
Helper III

Average from last 30 results

Hello everyone, 

 

This is my first post and the beginning of my adventure with dax so please be understanding. I have a table with 3 columns: index, score and player. I try to count the average score of the last 30 indexes for each player. I have over 14,000 lines and over 300 players so for each player the last 30 indexes will be different.

ScoresScores

 

Thank you for all the great content you have here - it is a great place to learn 🙂

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Pan_Forex , Create an index column in table using DAX

 

index= rankx(filter(Table, [Player] = earlier([Player]) ), [id],,asc,desc)

 

Create a new table

series = generateseries(Min(Table[index]), Max(Table[index]) ,1)

 

then create join series[value] with rank and create a measure

 

calculate(average(Table[game_score]), filter(all(series) , series[Value] >= max(series[Value]) -30 && series[Value] <= max(series[Value]) ))

View solution in original post

5 REPLIES 5
DimaMD
Solution Sage
Solution Sage

Hi, @Pan_Forex Can you provide sample data with sensitive data removed

 


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

Hello, yea sure 🙂 here you will find everything https://we.tl/t-X27MhOsCQD

amitchandak
Super User
Super User

@Pan_Forex , Create an index column in table using DAX

 

index= rankx(filter(Table, [Player] = earlier([Player]) ), [id],,asc,desc)

 

Create a new table

series = generateseries(Min(Table[index]), Max(Table[index]) ,1)

 

then create join series[value] with rank and create a measure

 

calculate(average(Table[game_score]), filter(all(series) , series[Value] >= max(series[Value]) -30 && series[Value] <= max(series[Value]) ))

Hi, thank you very much for your reply! When creating an index column, it displays an error: Unexpected value of the TIES argument in the RANKX function. The allowed values are SKIP and DENSE.

 

My function looks like this: 

index = rankx(FILTER('public players', [Player] = earlier([Player]) ), [id],,asc,desc)

I fixed the errors, corrected the relationships and everything works. Thank you very much! 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors