Average for last 30 results, 2 conditions

[Pan_Forex]

Hey guys, I would like to count the average score for each player separately for each race he played. I would like to take into account the last 30 games in which he played a particular race. How do I rewrite the following functions to make this possible? The current version counts well the average of each player's last 30 games. 

1. I created an index column: index= rankx(filter(Table, [Player] = earlier([Player]) ), [game_id],,asc,desc)

2. I created new table: series = generateseries(Min(Table[index]), Max(Table[index]) ,1)

3. I connected the two tables with a relation

4. i created a meassure: calculate(average(Table[score]), filter(all(series) , series[Value] >= max(series[Value]) -30 && series[Value] <= max(series[Value]) ))

Table looks like that:

I filtered out a single player to show what result I want to achieve:

[Pan_Forex]

If you want I can provide data and results of several players to make it easier for you to understand my logic

[tamerj1]

@Pan_Forex 

Please try returning the top 1 instead of thhe top 30. See if you get the last result. Also in this filter context there is no need for the CALCULATETABLE - ALLEXCEPT

AverageMeasure =
AVERAGEX (
TOPN (
1,
'Table',
'Table'[game_id]
),
'Table'[score]
)

[Pan_Forex]

In this form it only takes one result into account 😞 Maybe let's approach it differently. How do you count the average for each player and last 30 games played only as Romans?

[tamerj1]

@Pan_Forex 

If it works with top1 then it should work with top30. Please double check your results. 

[tamerj1 · Accepted solution]

Hi @Pan_Forex 

I don't see why do you need to create the index column and the series table. Please try

AverageMeasure =
AVERAGEX (
TOPN (
30,
CALCULATETABLE (
'Table',
ALLEXCEPT ( 'Table', 'Table'[player], 'Table'[race] )
),
'Table'[game_id]
),
'Table'[score]
)

[Pan_Forex]

Thank you so much for your reply. You are right, your solution is much simpler. Unfortunately, the average is still counted only from the last 30 games regardless of race. I would like the average to be counted from the last 30 games of Romans, last 30 games of Asians etc. It seems to me that I need 4 separate measures?

[tamerj1]

@Pan_Forex 

Would you please share a screenshot of your visual?

[Pan_Forex]

It seems to me that currently the problem occurs when someone has played more than 30 games with a particular race. Then the result is wrong.