Sum all child story points when an epic has a custom field value across all project epics

Questions & Answers
1

We use a custom field on our Epics that has predefined values in a drop down and you can select a single value. The custom field’s name is “Strategic Driver” and there are several predefined values.

I want to sum all story points under epics given the Strategic Drive for all epics.

I have a measure that will count the number of Epics given the Strategic Drive but ChatGPT and trying to configure based off of it’s output have yet to work.

So given in my Project there are 10 Epics and 3 of the Epics have custom field ‘Strategic Driver’ set to ‘Platform Investments’ and of the 2 Epics have custom field ‘Strategic Driver’ set to ‘Run the Business’ , etc. I want to count all child stories of those Epics as a measure and already have the Strategic Driver as a dimension. I will have that dimension as Rows and want the columns to display the total story points with a table report.
Table

The Epic Count measure is

sum([Issue Type].[epic].[Strategic Driver].CurrentHierarchyMember.Children)
2

Hello @Spencer_Tiller,

Thanks for posting your question!

I can suggest the following approach:

:arrow_right: Create a new JavaScript calculated custom field in eazyBI to pass down the Strategic Driver value from epics to their children.

Settings for the new field (see Epic level custom field):

Internal name: epic_strategic_driver
Display name: Epic Strategic Driver
Data type: string
Dimension: ✓

JavaScript code:

if(issue.fields.customfield_XXXXX) { // Replace XXXXX with your Strategic Driver field ID 
return issue.fields.customfield_XXXXX.value; }

:arrow_right: Create a new calculated measure:

Sum(
  Filter(
    Descendants([Issue.Epic].CurrentHierarchyMember,[Issue.Epic].[Epic]),
    ([Measures].[Issues created],
    [Issue Type].[Epic]) > 0
  ),
  DefaultContext((
    [Measures].[Story Points created],
    [Issue.Epic].CurrentHierarchyMember
  )) 
  - [Measures].[Issue Story Points]
)

This will:

  • Filter epics based on Strategic Driver value
  • Sum story points from their children
  • Subtract the epic’s own story points to only count child issues

Hope this helps!

Best,
Marita // support@eazybi.com

1 Like
3

This was exactly what I needed. Thank you!

1 Like