Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!The Power BI Data Visualization World Championships is back! It's time to submit your entry. Live 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
The Power BI Data Visualization World Championships is back! It's time to submit your entry.
| User | Count |
|---|---|
| 6 | |
| 5 | |
| 4 | |
| 4 | |
| 3 |
| User | Count |
|---|---|
| 12 | |
| 9 | |
| 8 | |
| 7 | |
| 7 |