How do I exclude Original Estimate value per result for tasks who have subtasks, but not for ones that don't

We use Jira and structure projects to use tickets in hierarchies:
Epics > Tasks > Subtasks

I have a report returning all items from a project. Using the Issue dimension with Epic set under “all hierarchy level members”, I’m able to get 99% of what I need. A table showing all of the Epic tickets (in table view) and each row expands to show associated children tasks. Those that have subtasks can be expanded again. Everything is being calculated properly given the info the report is looking at.

The issue is that for various reasons, our subtasks are given granular estimates in Jira. The tasks above them are then updated to set an estimate that is same as the sum of the subtasks. This creates a duplication issue in EasyBI because it’s counting both task and subtask estimates separately. We can’t stop doing this in Jira because it will cause issues with how we do our sprint planning.

I’m trying to find a way to create a calculated member or something that will effectively report 0 or ignore the estimate for any task that DOES have subtasks, but include it for any that DO NOT have subtasks.

P.S. I’ve tried using the measure for “Original Estimate hours with subtasks” as well, thinking it might help me negate the duplicate time, but it returns 0 for everything.

Hi @ctoler

Welcome to the Community! :slight_smile:

If your Jira planning causes double-counting at the Task level, you can use this formula to subtract Sub-task values from a Task value:

[Measures].[Original estimated hours]
-
Sum(
  [Issue].[Issue].GetMembersByKeys(
    [Issue].CurrentHierarchyMember.Get('Sub-task keys')
  ),
  [Measures].[Original estimated hours]
)

This formula retrieves all the “Sub-task keys” of an issue and gets all the Issues (in this case- sub-tasks) by these keys. Then, it sums up the Original estimated hours of these subtasks and subtracts them from the Task hours. The subtraction will only work in the case when there is an issue with sub-tasks. In any other scenario, the original measure will be returned as the subtracted value would be 0.

If you are also using the Epic level in your report, you can add to this formula so that the correct sum would be displayed for the Epics as well:

CASE WHEN
[Issue].CurrentHierarchyMember.Level.Name = "Epic"
THEN
Sum(
  ChildrenSet([Issue].CurrentHierarchyMember),
  (
    [Measures].[Original estimated hours]
    -
    Sum(
      [Issue].[Issue].GetMembersByKeys(
        [Issue].CurrentHierarchyMember.Get('Sub-task keys')
      ),
      [Measures].[Original estimated hours]
    )
  )
)
ELSE
[Measures].[Original estimated hours]
-
Sum(
  [Issue].[Issue].GetMembersByKeys(
    [Issue].CurrentHierarchyMember.Get('Sub-task keys')
  ),
  [Measures].[Original estimated hours]
)
END

This formula uses the CASE statement. In the case of an Epic, this formula, using the same previous formula will Sum the hours of all its children (ChildrenSet). And if the current Issue in Rows is not an Epic, the previous formula will be used.

Best regards,
Nauris / eazyBI support

Hi Nauris. Thanks!

I got the formula added as a new calculated member in use-defined measures area. That seems to successfully be factoring out the task-level estimates.

However, when I add the second formula as a calculated member to the “Issue” dimension (in lieu of “Epic” level), it removes all the children and just reports the total epic estimates. Is there a way to keep the general format and have it only adjust each parent number?

Also, I did a bit more testing with the first calculated measure (the one for excluding subtask estimates from a task line total). This is backwards.

Because of the way our team process works, we estimate subtasks but then add the total estimate to the parent task. However there are cases where the numbers differ due to human error and the subtasks are always more specific and generally more correct.

Is there any way to rewrite the measure to ignore the task estimate, as opposed to the subtasks? I ran the numbers manually and they come out a little different than the measure you gave, which tells me there are a few outliers where the task estimate vs subtask totals are different. Thanks in advance!

Hi,

Sorry, I didn’t mention this, but all formulas that perform calculations with different measures should be used in user-defined calculated measures in the Measures dimension.
These formulas will not work in Issue and other dimensions.

I may have misunderstood the original task at hand, as the previously provided formulas will work as expected only if all of your Tasks that have Sub-tasks have duplicate values.

You can define a new calculated measure in the Measures dimension with the following formula to use only the Sum of Sub-task hours for Tasks that have Sub-tasks instead of their own hours:

--annotations.total=sum
[Measures].[Original estimated hours]
-
Sum(
  Filter(
      Descendants([Issue.Epic].CurrentMember,[Issue.Epic].[Parent]),
      Not IsEmpty([Measures].[Issue sub-task keys])
      AND
      Sum(Childrenset([Issue].CurrentMember),
        [Measures].[Original estimated hours]
        )>0
    ),
  Sum((
    [Issue].Currentmember,
    [Measures].[Original estimated hours],
    [Issue Type.By type].[Standard]
  ))
)

Note that this formula should only be used together with the Epic hierarchy of the Issues dimension, as it is built for iterating the members of this hierarchy:

If the data in your Jira is correct, then this measure will produce the same results as “Original estimated hours” measure. However, if there are some inconsistencies in some Tasks, this measure will show the correct sum of the Sub-tasks.

Best regards,
Nauris