# Deviation between original estimated vs hours spetn

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

Hi, I have calculate:
Hours spent with time dimension:
Sum(
Filter(
Descendants([Issue].CurrentMember,[Issue].[Issue]),
DateInPeriod(
[Issue].CurrentHierarchyMember.Get(âClosed atâ),
[Time].CurrentHierarchyMember
)
),
)
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:

Thanks

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

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
)
),
)

Sum(
Filter(
Descendants([Issue].CurrentMember,[Issue].[Issue]),
DateInPeriod(
[Issue].CurrentHierarchyMember.Get(âClosed atâ),
[Time].CurrentHierarchyMember
)
),
)

Thanks

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

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

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
),