Deviation between original estimated vs hours spetn

Questions & Answers
1

Hi,
Is there a formula to calculate de deviation between original estimated and hours spent?
Thanks

2

Hi, I have calculate:
Hours spent with time dimension:
Sum(
Filter(
Descendants([Issue].CurrentMember,[Issue].[Issue]),
DateInPeriod(
[Issue].CurrentHierarchyMember.Get(‘Closed at’),
[Time].CurrentHierarchyMember
)
),
([Measures].[Hours spent with sub-tasks],[Time].CurrentHierarchy.DefaultMember)
)
Same for original estimate
and then the deviation:

CASE WHEN [Measures].[Original estimate with subtask sum at closed date] > 0 THEN

([Measures].[Hours spent with subtask sum at closed date]-[Measures].[Original estimate with subtask sum at closed date])/[Measures].[Original estimate with subtask sum at closed date]

END

but something is not running well because when I agrupate with issuetype dimension and issue deviation is not the same, for example:

image
image935×219 8.72 KB

image
image893×233 6.75 KB

Thanks

3

Hi @aelexpuru ,
Your last formula compares all the original estimates from the period with all hours spent in the same period, even if the issue didn’t have any original estimate.
For example, you have 3 issues that have been closed in the period, all three have hours spent (for example, 5h per each), but only one issue has the original estimate of 5h. Your formula would calculate the period like this:
((3x5h) - 5h)/5h = 200%

And that works fine when you don’t drill through issues. When you drill through issues then, only one issue is returned where the original estimate is not empty because of this condition:

CASE WHEN [Measures].[Original estimate with subtask sum at closed date] > 0 
THEN
....
END

If you want to compare only original estimates with hours spent on the same issues, then you need to iterate through issues and use a filter to filter only issues where the original estimate is greater than 0:

Avg(
  Filter(
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
      [Measures].[Original estimate with subtask sum at closed date] > 0
  ),
  ([Measures].[Hours spent with subtask sum at closed date]
  -[Measures].[Original estimate with subtask sum at closed date])
  /[Measures].[Original estimate with subtask sum at closed date]
)

Best,
Gerda // support@eazybi.com

4

Hi @gerda.grantina ,
Thanks for the answear is usefull for me, but know I have another doubt:
I have 2 measures but in the same report do not sum the same issues, why can be?
measures:
Sum(
Filter(
Descendants([Issue].CurrentMember,[Issue].[Issue]),
DateInPeriod(
[Issue].CurrentHierarchyMember.Get(‘Closed at’),
[Time].CurrentHierarchyMember
)
),
([Measures].[Hours spent with sub-tasks],[Time].CurrentHierarchy.DefaultMember)
)

Sum(
Filter(
Descendants([Issue].CurrentMember,[Issue].[Issue]),
DateInPeriod(
[Issue].CurrentHierarchyMember.Get(‘Closed at’),
[Time].CurrentHierarchyMember
)
),
([Measures].[Original estimated hours with sub-tasks],[Time].CurrentHierarchy.DefaultMember)
)

image
image845×472 24 KB

Thanks

5

Hi @aelexpuru,
Do those issues have both measures, “Original estimated hours with sub-tasks” and “Hours spent with sub-tasks”? There could be a situation where one of those measures is empty, and thus the issue count differs; see my example above, where three issues have hours spent, but only one issue has both measures.
You can always drill through the return issues and see if they have both measures.

What is the outcome you would like to see? Only those issues that have both measures?

best,
Gerda

6

Hi @gerda.grantina ,

Issues can have hours spent and not original estimated, That will be the problem.
Yes I would like to see:
issues closed in one month gruped by issuetype.
The sum of original estimated time, the sum of hours spent and the deviation of the same issues
Thanks

7

Hi @aelexpuru ,
If you want to have the same issues, then you can filter that both measures are not empty with this expression:

Sum(
  Filter(
  Descendants([Issue].CurrentMember,[Issue].[Issue]),
    DateInPeriod(
    [Issue].CurrentHierarchyMember.Get("Closed at"),
    [Time].CurrentHierarchyMember
    )
    AND
    NOT IsEmpty([Measures].[Original estimated hours with sub-tasks])
    AND
    NOT IsEmpty([Measures].[Hours spent with sub-tasks])
  ),
  ([Measures].[Hours spent with sub-tasks],
  [Time].CurrentHierarchy.DefaultMember)
)

Similarly, you can create a measure for “Original estimated hours with sub-tasks”.

best,
Gerda

8

Thanks @gerda.grantina

1 Like