Original estimated hours and actual time logged

Questions & Answers
1

Hi!

I’m trying to build a report that will display projects, epics, and issues and show for them Original estimated hours and logged hours. The point is that I need to find out original estimates for those issues that at least one developer logged time for.
I created a calculated member using the following code:

NonZero(
Sum(
Filter(
Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
[Measures].[Tempo billed hours]>0
),
(
[Measures].[Original estimated hours],
[Logged by Group].DefaultMember,
[Logged by].DefaultMember
)
)
)

Owing to this member, I can see Original estimated hours for projects and epics within these projects, and also, I can see which Jira groups and which users logged time within projects and epics. However, I don’t see Original estimated hours on the level of separate issues.

Screen_Shot_2018-09-25_at_4_28_12_PM
Screen_Shot_2018-09-25_at_4_28_12_PM.png2080×846 36 KB

How can I improve the calculated member I created to get Original estimated hours displayed on all level, including issues?

Thanks!

2

Hi!

Thank you for the screenshot, it is helpful to identify the problem!

I see you are using the Epic hierarchy from Issue dimension. Still, in calculated member, the default issue hierarchy is used. The calculation should be changed to use the correct hierarchy and level in which the Original estimate is saved. For example, if Original estimate comes from Parent level, the formula would be as follows

NonZero(
Sum(
  Filter(
    Descendants([Issue.Epic].CurrentMember, [Issue.Epic].[Parent]),
    [Measures].[Tempo billed hours] > 0
  ),
  (
    [Measures].[Original estimated hours],
    [Logged by Group].DefaultMember,
    [Logged by].DefaultMember
  )
)
)

Lauma / support@eazybi.com

3

Thanks! Now it works properly.

1 Like
4

Hi Lauma, I have this report:

image
image.png1071×781 33.3 KB

The formula for the “Epic original estimated hours” is this:
(
[Measures].[Original estimated hours],
[Issue Type].[Epic]
)

The formula for the “REH - ST” is an attempted adaptation of your comment as follows:

NonZero(
Sum(
Filter(
Descendants([Issue.Epic].CurrentMember, [Issue].[Issue]),
[Measures].[Issues created] > 0
),
(
[Measures].[Original estimated hours with sub-tasks]
)
)
)

Basically, I want to get the sum of all “Original estimated hours with sub-tasks” for stories under the Epic.

But the formula for REH - ST field is giving the estimates at the epic level only. Could you be kind and help me with this, please?

For various filtering needs (for my managers) at the Epic level, I want to keep the report design and filters at the Epic level, but I want “Original estimated hours with sub-tasks”, “Remaining estimated hours with sub-tasks” and “Hours spent with sub-tasks” as the sum of all stories under the Epics.

I hope I have stated the question clearly enough! Please help me!

Best,
ssh

5

Hi @ssh!

I am sorry for not noticing your question earlier!

All the measures, also when used in the formulas, are looking at the report context. As you have selected to filter only Issue.Epic members that have Issues created greater than zero, all issues within Epic are filtered out as they have Issues created 0 for the selected Page context.

Instead, we can get all Epic issues ignoring the filter for that level and then use the DefaultContext(…) to also ignore the page selections for the Original estimated hours with sub-tasks. Please try the following formula with Issue.Epic hierarchy

 NonZero(
   Sum(
     Filter(Descendants([Issue.Epic].CurrentMember, [Issue.Epic].[Parent]),
    ([Measures].[Issues created], [Issue.Epic].CurrentMember.Parent, [Issue Type].[Epic]) > 0),
   DefaultContext((
     [Measures].[Original estimated hours with sub-tasks],
     [Issue.Epic].CurrentHierarchyMember
    ))
 ))

Lauma / support@eazybi.com

1 Like
6

Lauma, you are a diamond star! It worked and I am smiling from ear to ear. I am also learning a lot from that very complicated MDX you have presented. Thanks a lot.

I have one followup question: if I add the Time dimension to the page filter, to look at, for example, only EPICs that have been created in the past 1 year, would that cause the implementation to fail? In other words, will the Time dimension apply to the ‘children’ stories also, instead of ignoring page-level filter?

I am known to write very complicated sentences, but I just hope I have stated my question clearly enough!

Thank you once again for your help!

Best,
ssh

7

It is so nice to know that I have brought a smile to your face! :slight_smile:

The Time filter, like all other Page filters, will apply to the Epic level and not to the Original estimate for the Issues within the Epic.

Lauma / support@eazybi.com