Avg from a Variable Table

[askelton]

I need to find out how to calculate the average of a column of a table stored as a variable.  I have data stored in the same format as A-C in the example below.

 

I want to find out how to average the top 3 values of the 5 most recent dates for each group.   I think I have figured out how to get those top 3 values alone in a table stored as a variable, however I can't seem to average them.

I first created a table with a unique list of the groups:

 Averages = distinct(Data[Group])

Then I tried to make a calculated column as follows: 

Avgs = 
var GroupTable = filter(Data, Data[Group] = Averages[Group])
var DatesRankedTable = addcolumns(GroupTable, "DateRank", rankx(GroupTable, [Date]))
var Top5Dates = filter(DatesRankedTable, [DateRank] <= 5)
var ValueRankedTable = addcolumns(Top5Dates, "ValueRank", rankx(Top5Dates, [Value]))
var Top3Values = filter(ValueRankedTable, [ValueRank] <= 3)
return
Average(Top3Values[Value])

If I do all the variable tables manually step by step, the "Top3Values" table comes out as it should.  However then when I try to do the average of [Value] in "Top3Values" it errors out.  How can I get the average of a column that is in this table stored as a variable.

[askelton · Accepted solution]

I'm not sure why I can't average a column from a variable table.  However I was able to get the result I wanted by using:

Avgs = 
var GroupTable = filter(Data, Data[Group] = Averages[Group])
var DatesRankedTable = addcolumns(GroupTable, "DateRank", rankx(GroupTable, [Date]))
var Top5Dates = filter(DatesRankedTable, [DateRank] <= 5)
var ValueRankedTable = addcolumns(Top5Dates, "ValueRank", rankx(Top5Dates, [Value]))
var Top3Values = filter(ValueRankedTable, [ValueRank] <= 3)
var AverageValue = sumx(Top3Values[Value])/countrows(Top3Values)
return
AverageValue