cancel
Showing results for
Did you mean:
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.

Scores

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

1 ACCEPTED SOLUTION
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
5 REPLIES 5
Super User

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

__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine
Helper II

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

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
Helper II

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)

Helper II

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

Announcements

#### 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.

#### 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!

#### 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