Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register 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)+0
What 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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
21 | |
17 | |
16 | |
11 | |
7 |
User | Count |
---|---|
26 | |
25 | |
12 | |
12 | |
12 |