Hi All,
I am trying to get the velocity for a roll up of sprints. You can see that when I expand the sprints it shows the velocity each sprint but when I collapse the sprints it doesn’t show the velocity.
I am using this for Velocity:
CASE
WHEN
[Sprint].CurrentHierarchyMember.Level.Name = ‘Sprint’ AND
[Sprint].CurrentHierarchyMember.GetBoolean(‘Closed’)
THEN
AVG(
Tail(
Filter(
– filter last 5 previous sprints in a board starting from current sprint
[Sprint].CurrentHierarchyMember.FirstSibling:
[Sprint].CurrentHierarchyMember,
[Sprint].CurrentHierarchyMember.GetBoolean(‘Closed’)
),
– set the count of last closed sprints for running velocity
5 ) ,
[Measures].[Completed Story Points with out Bugs]
)
END
How can I modify this measure to show a roll up?
Thanks!
Hi @Kayris06
The current formula is designed to show results only at sprint level name for Sprint dimension members.(Line 3 in the formula defines it).
Aggregates (calculated members in Sprint dimensions) have level.name = “(All)”
But the most important question here is about the method you want to use on the aggregate level since you are asking about a dynamic calculation - “Running Velocity for 5 closed sprints”. This calculation dynamically calculates the result for 5 last closed sprints of particular sprint row. So it is not clear how to calculate the same for aggregate row.
For example, if your calculated member “23.3 Sprints” is expanded, I can see it has 6 sprints inside. Each of sprints has a different result in “Running Velocity for last 5 closed sprints” calculated from previous 5 sprints before that particular sprint. This result is calculated from different sprints.
So, if we look at the aggregate row, what would be the “last 5 closed sprints” - set for calculation?
Is it sprints from the same aggregate, is it sprints from other boards.
Please explain how you would calculate it for “23.3 Sprints” mathematically, and I will try to give you some ideas on how to do the same with eazyBI formulas.
Is it the sum of the six sprints inside that aggregate? Or is it perhaps the average of 6 children? Or maybe even running velocity for the previous 5 aggregates (other calculated members)?
Martins / eazyBI
So I want the average velocity for that 23.3 Sprints based on the 6 sprint velocities under it and then I want the average of the velocities for all the parent rows. (23.3 Sprints, 23.4 Sprints…etc)
Hi @Kayris06
Do I understand you correctly, that you want to see the result from each of six sprint velocities calculations. Something like (48+47.5+45.33+43.75+45.4+44.6)/6 at 23.3 Sprints row?
What do you believe this number would represent in your business case? Also, wouldn’t the column header “Velocity for 5 closed sprints” be misleading if you calculate results from all closed sprints?
If yes, you can try this code
AVG(
DescendantsSet([Sprint].Currentmember,[Sprint].[Sprint]),
CASE WHEN
[Measures].[Sprint Issues completed]>0
THEN
[Measures].[Running Velocity for 5 Closed sprints]
END
)
Although it should mathematically do what you expect, I don’t quite understand what this would result represents - some average of dynamic velocities. But maybe it is just me 
Martins / eazyBI
Yes to the first part. That is what I would want to see in the 23.3 Sprints Row. I want to try that at least.
However now that I think of it that number should probably be internal and used to create an average over all the parent rows and it would change as a new set is introduced. I don’t even know how to do that.
The title of the measure I had was the standard velocity measure that I used as a starting point. It will be titled differently once I can get the calculations in.
Thanks
