How to calculate roll up average using dax

Hi Anonymous , 

First you need to create index column by group:

  #"Grouped Rows" = Table.Group(#"Removed Columns1", {"Team"}, {{"Count", each _, type table [Team=nullable text, DateBegin=nullable date, Sprint=nullable text, Points=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"index",1)),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom",{"Team", "Count"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns2", "Custom", {"Team", "DateBegin", "Sprint", "Points", "index"}, {"Custom.Team", "Custom.DateBegin", "Custom.Sprint", "Custom.Points", "Custom.index"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Expanded Custom",{{"Custom.Team", "Team"}, {"Custom.DateBegin", "DateBegin"}, {"Custom.Sprint", "Sprint"}, {"Custom.Points", "Points"}, {"Custom.index", "index"}})

Then you can use the following measure:

Measure = MAX('Table'[Points])- AVERAGEX(TOPN(3,FILTER(ALLEXCEPT('Table','Table'[Team]),'Table'[index] <MAX('Table'[index])),'Table'[index],DESC),'Table'[Points])

Please refer to the pbix file.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai