I need to show a dashboard showing Task Progress.
Users are manually entering the values at the child levels.
Now I need to compute the average at the parent level.
If I use below, I can’t get the sub-tasks
Descendants([Issue.Epic].DefaultMember, [Issue.Epic].[Parent])
If I use this, I only get the sub-tasks
Descendants([Issue.Epic].DefaultMember, [Issue.Epic].[Sub-task]
Is there a way to combine both?
Basically if the task has no children, get that issue’s Task Progress.
If the task has sub-tasks, get the average of sub-tasks’ Task Progress.
Then compute the average Task Progress of each Task and assign to the Epic.
Is this the right approach?
Any help will be greatly appreciated!
In this case, you might want to calculate the Task progress first at the Parent level and then reuse the results for Epic level calculation.
Create a new calculated measure to calculate Parent progress based on the subtasks, if any. I named this measure “Parent progress” to use it in further calaultions:
CASE WHEN -- the issue has parent level and has any sub-tasks
[Issue].CurrentHierarchyMember.Level.Name = "Parent" AND
[Issue].CurrentHierarchyMember.Children.Count > 0
THEN --get the average of subtasks
Sum(
[Issue].CurrentHierarchyMember.Children,
[Issue].CurrentHierarchyMember.Get('Individual progress')
) /
--divide by all sub-task count
[Issue].CurrentHierarchyMember.Children.Count
ELSE [Issue].CurrentHierarchyMember.Get('Individual progress')
END
Now you can create calculations to get compute Epic progress based on it child issue progress. The logic is similar to the formula above.
CASE WHEN --epic has child issues
[Issue].CurrentHierarchyMember.Children.Count > 0
THEN --get average of progress at parent level
Sum(
[Issue].CurrentHierarchyMember.Children,
[Measures].[Parent progress] --here reference to previous measures
) /
--divide by all parent count in epic
[Issue].CurrentHierarchyMember.Children.Count
ELSE --show epic or sub-task progress as it is assigned to it
[Issue].CurrentHierarchyMember.Get('Individual progress')
END
Both calculations are based only on issue properties and would work when individual issues (epics or parent tasks) are on report rows.
Thanks Zane!
I am getting the values from the field Task Progress which I imported as both property and measure.
I couldn’t get this line to work: [Issue].CurrentHierarchyMember.Get(‘Issue Task progress’).
I used [Measures].[Issue Task progress] instead and I am getting the desired values.
When I add a sub-task to the Epic, I am getting the results below:
@Howell, all issues have a dedicated place in the issue hierarchy according to its type.
For example, if the Sub-task is added directly to the Epic, then the middle layer of “Parent” is filled with the epic itself as it is a parent of the Sub-task.
If you want to be picky on which child issues should be counted for the progress calculation, you may add additional filter criteria to the calculations. Add function Filter() and check on the issue type for epic children (issues at the Parent level). In your case, you might want to filter out parent issues with type “Epic” because those are placeholders fr directly linked sub-tasks.
The updated calculation for Epic progress might look like this:
CASE WHEN --epic has child issues with specified issue type
Count(
Filter([Issue].CurrentHierarchyMember.Children,
[Measures].[Issue type] NOT MATCHES "Epic")
) > 0
THEN --get average of progress at parent level
Sum(
[Issue].CurrentHierarchyMember.Children,
[Measures].[Parent progress] --here reference to previous measures
) /
--divide by all specified type parent count in epic
Count(
Filter([Issue].CurrentHierarchyMember.Children,
[Measures].[Issue type] NOT MATCHES "Epic")
) > 0
ELSE --show epic or sub-task progress as it is assigned to it
[Measures].[Issue Task progress]
END
