How to exclude sub-tasks story points from the story point completed?

Hello !

I want to calculate how many points by sprint my teams commited and delivered, but without story points that are evaluate in the sub-tasks.

For example my team commited to 16 points in the parent issue, and splitted those 16 points in the sub-tasks. The total with the classical Sprint Velocity chart in EazyBI will be 32, because it is based on story point commited >0, instead of 16 (sprint report in JIRA excluse subtask’s story point).

So how to exclude sub-tasks story points from the story point completed an commited ?

Thanks !

Hi @Pierre-Antoine_Laine

Welcome to eazyBI community page.
In this case, you could create new calcualted member in “Issue type” dimension that aggregates just the standard issue types.

see some examples on our demo account:
https://eazybi.com/accounts/1000/cubes/Issues/

Aggregate(
Filter(
  [Issue type].[Issue type].Members,
  NOT [Issue type].CurrentMember.GetBoolean('Subtask')
  ))

Then you could enable this member for your report page filters and use it to exclude sub-tasks from the calculation.

Martins / eazyBI support

1 Like

hi @martins.vanags
how can i calculate the story points of subtask only in my eazy BI report

Hi @romabakshi

Try creating a new calculated measure using the following formula.

Aggregate(
Filter(
  [Issue type].[Issue type].Members,
  [Issue type].CurrentMember.GetBoolean('Subtask')
  ),
[Measures].[Story points created]
  )

It should calculate story points for sub-tasks.

Martins / eazyBI team

Thanks @martins.vanags - this works well for me. Further i am trying to create a query where my report should show the estimated story points of the user Story only if there is no sub task in the story like
the report will pick the story points at the sub task level and if any story doesnt have the sub task then only it will pick the user story - estimated story points.
i tried the below but not working

CASE WHEN [Measures].[Sub-Task Story Points] > 0
THEN
([Measures].[Estimated Story Points created],[Issue Type].[Sub-task])
ELSE
([Measures].[Estimated Story Points created],[Issue Type].[Story])
END

TIA

Perhaps you can use the “issue.Sub-task” hierarchy in your report. That would automatically aggregate values from the sub-tasks and show on the story level.

Martins / eazyBI support

Thanks @martins.vanags for the response but my requirement is different. I need the data in measures where
Condition 1 - If a user story has a subtask then only Subtask story points will be picked only and ignoring its user story level story points

Condition 2 - If a user story has no subtask then only story points of the user story will be picked

Try creating a calcualted measure using this formula

CASE WHEN [Measures].[Sub-tasks created] > 0
THEN
Nonzero(([Measures].[Estimated Story Points created],[Issue Type].[Sub-task]))
ELSE
Nonzero([Measures].[Estimated Story Points created])
END

And keep the same Issue.sub-task hierarchy in the report.

Martins / eazyBI team

Hi @martins.vanags ,

I was trying this solution as I have exactly the same issue. For issue type Story, when there are Sub-Tasks present I want to count their Story Points by Target Launch Date, and when there are none, I want to count the Story Points by Target Launch Date of the main Story. I modified your suggestions as follows:

CASE WHEN [Measures].[Sub-tasks created] > 0
THEN
NonZero(([Measures].[Story Points with target launch date],[Issue Type].[Sub-task]))
ELSE
NonZero([Measures].[Story Points with target launch date])
END

I thought it was working just fine but now I have an example of a story with many sub-tasks where it seems to be counting both the sub-tasks and the main story.

Any advice?

Thank you!
Jared

Hi @jared.barnes

Please provide the report definition
https://docs.eazybi.com/eazybi/analyze-and-visualize/create-reports#Createreports-Exportandimportreportdefinitions

Martins / eazyBI

Hoping this is correct!

{
“cube_name”: “Issues”,
“cube_reports”: [ {
“name”: “SP by launch date weekly open only”,
“result_view”: “timeline_chart”,
“definition”: {“columns”:{“dimensions”:[{“name”:“Measures”,“selected_set”:["[Measures].[SPbyLaunchDateSub-taskFirst]","[Measures].[106 SP reasonable maximum per week]"],“members”:[]},{“name”:“Hard Dated”,“selected_set”:["[Hard Dated].[All Hard Dateds]","[Hard Dated].[Yes]"],“members”:[],“bookmarked_members”:[{“depth”:1,“name”:“Yes”,“full_name”:"[Hard Dated].[Yes]"}]}]},“rows”:{“dimensions”:[{“name”:“Time”,“selected_set”:["[Time.Weekly].[Week].Members"],“members”:[],“bookmarked_members”:[{“depth”:3,“name”:“Apr 2020”,“full_name”:"[Time].[2020].[Q2 2020].[Apr 2020]",“drillable”:true},{“depth”:3,“name”:“May 2020”,“full_name”:"[Time].[2020].[Q2 2020].[May 2020]",“drillable”:true},{“depth”:3,“name”:“Jun 2020”,“full_name”:"[Time].[2020].[Q2 2020].[Jun 2020]",“drillable”:true}]}],“filter_by”:{“conditions”:[{“expression”:"[Time.Weekly].CurrentHierarchyMember",“operator”:“between”,“value”:“one month ago and two months from now”,“value_type”:“date”}],“others”:false},“nonempty_crossjoin”:false},“pages”:{“dimensions”:[{“name”:“Status”,“selected_set”:["[Status].[OpenNoHoldTraNoIntake]"],“members”:[{“depth”:0,“name”:“OpenNoTranslationNoHoldNotSubmitted”,“full_name”:"[Status].[OpenNoHoldTraNoIntake]",“calculated”:true,“drillable”:true,“dimension”:“Status”}],“bookmarked_members”:[{“depth”:1,“name”:“Closed”,“full_name”:"[Status].[Closed]"},{“depth”:1,“name”:“Cancelled”,“full_name”:"[Status].[Cancelled]"}],“current_page_members”:["[Status].[OpenNoHoldTraNoIntake]"]}]},“options”:{“nonempty”:true},“view”:{“current”:“timeline_chart”,“maximized”:false,“timeline_chart”:{“series_type”:“spline”,“series_options”:{},“y_axis”:{“0”:{“title”:“Total story point velocity versus team capacity”}}},“table”:{}},“calculated_members”:[]}
} ],
“calculated_members”: [{“name”:“CapacityUsed”,“dimension”:“Measures”,“formula”:"[Measures].[Story Points with target launch date] / 7",“format_string”:"#0%"},{“name”:“OpenNoHoldTraNoIntake”,“dimension”:“Status”,“formula”:“Aggregate(\n Except(\n [Status].[Status].Members,\n { [Status].[Closed],\n [Status].[Cancelled],[Status].[Translation],\n [Status].[On Hold], [Status].[Submitted], [Status].[Intake]\n\n }\n )\n)”,“format_string”:""},{“name”:“106 SP reasonable maximum per week”,“dimension”:“Measures”,“formula”:“106”,“format_string”:""},{“name”:“SPbyLaunchDateSub-taskFirst”,“dimension”:“Measures”,“formula”:“CASE WHEN [Measures].[Sub-tasks created] \u003e 0\nTHEN\nNonZero(([Measures].[Story Points with target launch date],[Issue Type].[Sub-task]))\nELSE\nNonZero([Measures].[Story Points with target launch date])\nEND”,“format_string”:"#,##0.00"}]
}

@jared.barnes

The code I shared earlier was meant for issue-level report.
Your definition doesn’t use issue level in the report, so another approach is required where calculation iterates through issues (even if the “Issue” dimension is not used in the report).
Try this formula in your case:

NonZero(
SUM(
Filter(
Descendants([Issue.Sub-task].CurrentMember,[Issue.Sub-task].[Parent]),
[Measures].[Story Points with target launch date]>0
),
CASE WHEN [Measures].[Sub-tasks created] > 0
THEN
NonZero(([Measures].[Story Points with target launch date],[Issue Type].[Sub-task]))
ELSE
NonZero([Measures].[Story Points with target launch date])
END
)
)

When selecting this new measure make sure that “Nonempty” cross join is enabled in your report.
https://docs.eazybi.com/eazybijira/analyze-and-visualize/create-reports#Createreports-Pagedimensions

Martins / eazyBI

Hi @martins.vanags ,

Thanks so much for your help. I think it may be a bit over my head! I’ve tried the formula on a different calculated measure, and selected ‘Nonempty’ on the Rows, which is Time in this instance, but it’s still giving me the same total as before, i.e. it’s not excluding the Story Points associated with the Story even when it has Sub-tasks.

I’m getting timeouts when I try to drill through the issues using the measure and wonder if there’s another setting that’s impacting the measure - not sure if the filter I have on or the customized fields I’m using are causing an issue.

I really appreciate the help. Sorry if I’m not quite understanding something!

Thanks,
Jared