Advance your Data & AI career with 50 days of live learning, dataviz contests, hands-on challenges, study groups & certifications and more!
Get registeredGet Fabric Certified for FREE during Fabric Data Days. Don't miss your chance! Request now
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)+0What am I doing wrong here?
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
@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.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!
Check out the October 2025 Power BI update to learn about new features.
| User | Count |
|---|---|
| 8 | |
| 8 | |
| 6 | |
| 5 | |
| 4 |
| User | Count |
|---|---|
| 25 | |
| 10 | |
| 8 | |
| 8 | |
| 8 |