SUM last 5 records

[Anonymous]

Data Structure: I have grouped each individual and assigned a sub-group index value in power query to each result for that individual (see example below) 

Table Name : 'Results'

Name	Force (N)	Sub-Group Index
Bill	130	1
Bill	140	2
Bill	150	3
Bill	130	4
Bill	125	5
Bill	140	6
Ted	180	1
Ted	155	2
Ted	140	3
Ted	130	4
Ted	160	5
Ted	170	6
Ted	170	7

Aim: I wish to SUM the values of the results table based on the last 5 records for eac individual. My logic of including a sub-group Index was to to try and calculate the sum based off the TOPN ranked DESC - not sure if this is a good approach?

Any help would be greatly appreciated!

[lbendlin]

yes, that's a good approach. Here is one possible implementation

Last5 = 
var n = SELECTEDVALUE(Results[Name])
var t = TOPN(5,filter(all(Results),Results[Name]=n),Results[Sub-Group Index],DESC)
return sumx(t,[Force (N) ])

It doesn't give you nice totals. Not sure if that is important. 

[lbendlin · Accepted solution]

Here is a version that also handles the totals correctly.

Last5v2 = 
var n = SELECTCOLUMNS(VALUES(Results[Name]),"Name",Results[Name])
var s = ADDCOLUMNS(n,"v",sumx(TOPN(5,filter(Results,Results[Name]=[Name]),Results[Sub-Group Index],DESC),[Force (N) ]))
return sumx(s,[v])

[Anonymous]

@lbendlin You sir are a genius! It works perfectly and the way you've laid it out it makes so much sense. Thank you so much!