Epic-Level Estimate

Hi Team,
I want the Epic-level “Original Estimate” field in a report column. In my Rows, I have the “Epic Link” dimension. In the columns, among other fields, I have “Original estimated hours with sub-tasks” and “Remaining estimated hours with sub-tasks.” As an additional field, I would like the Original Estimate, from the Epic (not the sum of the estimates of stories) in hours.

I tried adding a simple calculated member called “Epic Estimate” with the following:
[Epic Link].CurrentMember.Get(‘Original Estimate’). However this is not returning anything (probably NULL.)

Please help me!

Best,
Shyam

Hi,

In this case, you could try using the default measure “original estimated hours” together with “Issue” dimension epic level members.

Martins / eazyBI support

Hello Martins!
I am pretty sure I am doing it right, but you can validate:

I’m expecting to see 40.00 in the Original Estimated Hours field, because that is the estimate the team entered at the Epic:

Please help me! Thank you very much for your kind and quick response.

Best,
Shyam

Hi,

In that case, drag “Issue type” dimenion to pages area and filter your report by “Epic” (issue type).
The calculation should return results only from epics

Martins / eazyBI support

That’s giving me the estimate at the Epic level, yes! But since I also need the sum of estimated hours with sub-tasks for all the issues under the Epic, I added a calculated member called “Hours with Sub-Tasks” and used this formula:

case
when
[Issue].CurrentHierarchyMember.level.Name = “Epic”
and
([Issue Type].[Epic],
[Measures].[Issues created])>0
then
Sum([Issue].CurrentHierarchyMember.Children,
[Measures].[Original estimated hours with sub-tasks])
+
nonzero((
[Measures].[Original estimated hours with sub-tasks],
[Issue Type].[Epic]
))
end

But I am getting the same number 40, instead of something like 360 or 370. Could you be kind and point out what’s wrong with the formula, please?

Many thanks,
Shyam

Hi,

In that case, try a different approach

  1. remove “Issue Type” dimension (and filter) from your report

  2. Now “Original estimated hours” measure would return the total for each epic (including children)

  3. create a new calculated measure “Epic original estimated hours” using this code:

    (
    [Measures].[Original estimated hours],
    [Issue Type].[Epic]
    )

The same you could do with “Hours spent” measure.

Martins

1 Like

Is this problem solved? I have the same problem like ssh. As soon as I have epics and subtasks, I only get the sum of all subtasks at Epic level.
I have tried all the suggestions listed here.

Regards

Hi @Werner_Zecchino,

If you would use “Epic” level members from “Issue” dimension in your report (and don’t use “Issue Type” dimension to filter) it should aggregate all the value from original estimated hours from all the children including epic itself.

Could you please export and share the report definition?
Here is how you can export the definition:
https://docs.eazybi.com/display/EAZYBI/Create+reports#Createreports-Exportandimportreportdefinitions

Martins / support@eazybi.com

Hi

Enclosed the definition.

{
“cube_name”: “Issues”,
“cube_reports”: [ {
“name”: “Aufwand”,
“result_view”: “table”,
“definition”: {“columns”:{“dimensions”:[{“name”:“Measures”,“selected_set”:["[Measures].[Issue type]","[Measures].[Issue status]","[Measures].[Epic original estimated hours]"],“members”:[]}]},“rows”:{“dimensions”:[{“name”:“Issue”,“selected_set”:["[Issue.Epic].[Epic].Members"],“members”:[{“depth”:2,“name”:“SCHAMISDEV-3”,“full_name”:"[Issue.Epic].[SCHAMIS Development].[SCHAMISDEV-3]",“drillable”:true,“key”:“SCHAMISDEV-3”,“expanded”:true,“drilled_into”:false}],“bookmarked_members”:[]}],“nonempty_crossjoin”:true},“pages”:{“dimensions”:[]},“options”:{},“view”:{“current”:“table”,“maximized”:false,“table”:{}}}
} ],
“calculated_members”: [{“dimension”:“Measures”,“name”:“Issue type”,“format_string”:"",“formula”:"[Issue Type].[Issue Type].getMemberNameByKey(\n [Issue].CurrentHierarchyMember.get(‘Issue type ID’)\n)",“annotations”:{“group”:“Issue properties”}},{“dimension”:“Measures”,“name”:“Issue status”,“format_string”:"",“formula”:"[Status].[Status].getMemberNameByKey(\n [Issue].CurrentHierarchyMember.get(‘Status ID’)\n)",“annotations”:{“group”:“Issue properties”}},{“name”:“Epic original estimated hours”,“dimension”:“Measures”,“formula”:"[Measures].[Original estimated hours],\n[Issue Type].[Epic]",“format_string”:"#,##0.00"}]
}

At level Epic I need the entered value without aggregating the subtask. For the subtask also the entered value

Regards

Hi,

Try creating a new calculated measure using this code:

Aggregate(
  {
Filter(
  [Issue type].[Issue type].Members,
  NOT [Issue type].CurrentMember.GetBoolean('Subtask')
  )
  },
 [Measures].[Original estimated hours]
)

That should exclude sub-task original hours from all the levels in your report

Martins / support@eazybi.com

Hi Martins

It’s not working yet. I don’t think that expression works: [Issue type].[Issue type].Members,

I don’t understand the concept yet. What would be the idea?

Hi,

The idea is to exclude “Sub-task” level original hours for each member (epic, story) from the tree.
Could you please share more details on what exactly didn’t work?

Martins / eazyBI support

The task stil shows the estimation including the sub-task.

{
“cube_name”: “Issues”,
“cube_reports”: [ {
“name”: “Aufwand”,
“result_view”: “table”,
“definition”: {“columns”:{“dimensions”:[{“name”:“Measures”,“selected_set”:[“[Measures].[Epic original estimated hours]”],“members”:}]},“rows”:{“dimensions”:[{“name”:“Issue”,“selected_set”:[“[Issue.Epic].[Epic].Members”],“members”:[{“depth”:2,“name”:“SCHAMISDEV-3”,“full_name”:“[Issue.Epic].[SCHAMIS Development].[SCHAMISDEV-3]”,“drillable”:true,“key”:“SCHAMISDEV-3”,“expanded”:true,“drilled_into”:false}],“bookmarked_members”:}],“nonempty_crossjoin”:true},“pages”:{“dimensions”:},“options”:{},“view”:{“current”:“table”,“maximized”:false,“table”:{}}}
} ],
“calculated_members”: [{“name”:“Epic original estimated hours”,“dimension”:“Measures”,“formula”:“Aggregate(\n {\nFilter(\n [Issue type].[Issue type].Members,\n NOT [Issue type].CurrentMember.GetBoolean(‘Subtask’)\n )\n },\n [Measures].[Original estimated hours]\n)”,“format_string”:“#,##0.00”}]
}

Hi,

I find it quite strange that none of your stories have sub-tasks in the attachment (no possibility to expand to sub-task level) which makes me think that there is no issue type with “subtask” value imported in the account.

Do you find members at the sub-task level in the hierarchy at all (see attachment below)?

Martins / eazyBI support

The struture is:

Epic

  • Issues in Epic
  • like Tasks and Stories

Hi @Werner_Zecchino

I think there is some kind of misunderstanding here since you don’t have sub-tasks in your structure at all.
Please try creating a calculated measure with this code:

CASE WHEN
[Issue].Currenthierarchymember.level.name = "Epic"
THEN
nonzero((
[Measures].[Original estimated hours],
[Issue Type].[Epic]
))
WHEN
[Issue].Currenthierarchymember.level.name = "Parent"
OR
[Issue].Currenthierarchymember.level.name = "Sub-task"
THEN
Nonzero([Measures].[Original estimated hours])
END

That would show epic’s hours only for epic level and the user and story level hours for children of epic
Otherwise, please reach out to support@eazybi.com so we can schedule a debugging call.

Martins / eazyBI

Hello and thank you very much. Now it works.