cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Pan_Forex
Helper II
Helper II

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



Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines

View solution in original post

5 REPLIES 5
DimaMD
Super User
Super User

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

 


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

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



Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines

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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors