topic Re: Average size delivered per person for the last 3 sprints in DAX Commands and Tips

Average size delivered per person for the last 3 sprints

SquidgyWidget — Fri, 08 Dec 2023 02:45:27 GMT

I need a measure to calulate the average size of cards delivered per person for the last 3 sprints to assist with Agile sprint planning. Sample data might look like below:

The results from below meaure does not correctly calculate.

_AvgPointsDelivered = var _Last3Sprints = SUMMARIZE( FILTER( SUMMARIZE( 'Sprint Snapshots', 'Sprint Snapshots'[Sprint], "Rank", RANKX( ALLEXCEPT('Sprint Snapshots', 'Sprint Snapshots'[Column]), CALCULATE( MAX ('Sprint Snapshots'[Sprint])), , DESC, Dense ) ) , [Rank] <= 3) , [Sprint]) var _AvgPointsDelivered = DIVIDE( CALCULATE( sum('Sprint Snapshots'[Size]) , 'Sprint Snapshots', 'Sprint Snapshots'[Sprint] in _Last3Sprints , 'Sprint Snapshots', 'Sprint Snapshots'[Column] = "Done" ) , 3) return CALCULATE(_AvgPointsDelivered)+0

What am I doing wrong here?

Re: Average size delivered per person for the last 3 sprints

Fowmy — Fri, 08 Dec 2023 06:52:38 GMT

SquidgyWidget

Share more data in Excel or Csv and show the expected result for a few users. You can share the file including a dummy PBI using Google Drive or One Drive, past the link here.

Re: Average size delivered per person for the last 3 sprints

sjoerdvn — Fri, 08 Dec 2023 10:18:17 GMT

I think your issues are mainly caused by using 'Sprint Snapshots' as an argument to the calculate function.
try this:

AvgPointsDelivered = var _Last3Sprints = TOPN( 3, CALCULATETABLE(VALUES('Sprint Snapshots'[Sprint]),ALL()), 'Sprint Snapshots'[Sprint], DESC) var _AvgPointsDelivered = DIVIDE( CALCULATE( sum('Sprint Snapshots'[Size]) , _Last3Sprints , 'Sprint Snapshots'[Column] = "Done" ) , 3) return _AvgPointsDelivered +0