Average from last 30 results and 2 filters

Pan_Forex · ‎12-03-2022

Hey guys, I have a table with 4 columns: id, score, user and type. Each user can choose 4 different types(0,1,2,3). I'm trying to count the average score of the last 30 id for each player and separately 30 results for each type he chose.

id

user

type

score

I have already created an index column using the rankx function: index= rankx(filter(Table, [Player] = earlier([Player]) ), [id],,asc,desc)

I created a new table using generateseries: series = generateseries(Min(Table[index]), Max(Table[index]) ,1) and I connected them with the relation.

My actual measure looks like that now: calculate(average(Table[game_score]), filter(all(series) , series[Value] >= max(series[Value]) -30 && series[Value] <= max(series[Value]) ))

The problem is when I use the filter to select a type of, for example, 1 It only shows the average of a few results found in the last 30 id. I would like it to show the last 30 results only for type 0,1,2,3 and at the same time without selecting the filter it would show the average of the last 30 id without meaning the type

Pan_Forex · ‎12-09-2022

ID	User	Type	Score
60	A	0	1
59	B	1	2
58	A	0	2
57	B	0	2
56	C	2	2
55	E	0	2
54	D	1	2
53	A	2	2
52	A	1	2
51	A	2	2
50	B	0	2
49	B	1	2
48	A	1	2
47	G	1	2
46	A	1	2
45	A	1	2
44	A	1	1
43	B	2	1
42	A	1	1
41	A	2	1
40	A	2	5
39	A	2	2
38	A	3	4
37	B	3	5
36	B	1	6
35	A	1	5
34	A	1	2
33	E	1	5
32	C	2	2
31	A	2	3
30	A	2	4
29	A	1	2
28	A	1	1
27	A	1	5
26	A	2	3
25	G	3	1
24	A	2	5
23	B	3	1
22	A	3	2
21	A	3	5
20	A	1	1
19	A	1	2
18	B	1	1
17	A	4	5
16	A	4	2
15	A	4	3
14	A	2	2
13	B	2	2
12	A	1	2
11	A	1	1
10	A	1	1
9	A	4	5
8	C	4	5
7	A	1	5
6	A	1	2
5	A	0	3
4	A	0	5
3	A	0	3
2	A	0	5
1	A	0	1

At this point, if I count the score for the last 30(largest) IDs for each player, the result is fine. However, if I filter players by "Type 0,1,2,3", the results are only counted from the last 30 IDs and I would like it to show the last 30 types by ID.

lbendlin · ‎12-10-2022

Thank you for providing the sample data. Did you mean to say "the last 30 IDs by type"? Your sample data doesn't have enough data points for that.

Pan_Forex · ‎12-10-2022

Hi, thank you for your reply! I think so, it should work then. What more do I need? I have more than 14,000 lines and over 400 users. I just pasted a small piece. I have already counted the average score for each user from the last 30 IDs. It's works. The problem is that I do not consider the column type. When i add a fragmenter and select, for example, type 0, the filter only works in the area of just these last 30 IDs for each user. All I want when I select a filter is for it to show the last 30 types by IDs, not only inside the last 30 IDs of each user.