Forum Discussion

askelton's avatar
askelton
Resolver I
8 years ago
Solved

Avg from a Variable Table

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...
Need Help
  • askelton's avatar
    askelton
    8 years ago

    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

     

askelton's avatar
askelton
Resolver I
8 years ago

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