Display Average of custom field value of subtask at parent level

Questions & Answers
1

I’d like to configure a report in Jira Cloud that calculates the average workload of all sub-tasks and displays the integer average at the parent issue level.

Project Configuration:

  • Project Name: Test

  • Issue Types:

    • Standard Issue Types: Task, Story

    • Sub-task Issue Types: Electronic, Mechanical, Testing

  • Field: Workload (Dropdown field with values ranging from 1 to 10)

  • For each parent issue of type Story, we want to:

    • Calculate the average of the “Workload” field from its sub-tasks

    • Display this average (rounded to an integer) at the parent issue level in a custom field (e.g., Average Workload)

2

If the Workload is defined as a Number, you have to import it inside the configuration.
Then, it will create these Measures

So, you will only need to create a report with the rows each Issue and create this formula using the Filter:
Check how you are calling your Sub Tasks, “Sub-Task xxx” or “Sub Task xxx” or any other, if they are not like the below formula, this will return empty set.

Avg(
  -- set of issues to create the average
  Filter(
    Descendants([Issue]. CurrentHierarchyMember, [Issue].[Issue]),
    [Measures].[Workload] > 0
    -- This AND Part is optional, but filter a little bit more the query to retrieve only the Workload on these 3. You can skip if you have Workload only on these 3
    AND 
    (
      [Measures].[Issue Type] = "Sub-task Electronic" OR
      [Measures].[Issue Type] = "Sub-task Mechanical" OR
      [Measures].[Issue Type] = "Sub-task Testing"
    )
  ),
  -- field where you are calculating the AVG
  [Measures].[Workload]
)
3

Hello @jagruti , hello @Nacho

It seems that the user contacted us directly regarding this same question, and we’ve provided a solution through that channel.
I will paste the solution here in case other eazyBI users are trying to build something similar

The key consideration here is that Workload field is a dropdown field (which stores values as text/string), the values need to be converted to numeric format for the average calculation to work properly.

Here’s the measure formula we recommended for calculating the workload average from sub-tasks:

ROUND(
  Avg(
    Filter(
      [Issue].[Issue].GetMembersByKeys(
        [Issue].CurrentHierarchyMember.Get('Sub-task keys')
      ),
      [Measures].[Issues created] > 0 AND
      NOT IsEmpty([Measures].[Issue Workload])
    ),
    CASE
WHEN
[Measures].[Issue Workload] <> "(none)"
THEN
Cast([Measures].[Issue Workload] as numeric
)
END
  ),
  0
)

Best wishes,

Elita from support@eazybi.com