Calculating percent complete based on story point weighing using Automation

[Rimsha Khan]

Hello,

I am working on a automation (testing in One Atlassian) that will allow for a more accurate story point roll up based on story points rather than how many stories were  completed.

The automation should add up and average all the story points on child issues and update a custom field called "Percent Completed" on the Epic.

This is the smart value I plugged in for the field "Percent Completed" in my automation:

{{lookupIssues.Story Points.completed.sum.divide(lookupIssues.Story Points.total.sum).asPercentage}}

The automation is not populating the field. Could these be an issue of how the math expression was done using smart values?

Is there a better way to accomplish this using automations?

Here are screenshots of my automation: 

Thank you!

[Bill Sheboy · Accepted solution]

Hi @Rimsha Khan -- Welcome to the Atlassian Community!

FYI there are several posts in the community for this specific scenario, so it is often helpful to search around for those; some may exactly match what you need.

What you tried with dot-notation on the status / completed state will not work as "completed" is not a function.  Instead either multiple lookups are needed or filtering can be used.

Back to your question, there are several ways to solve this, and to minimize the changes to your rule you could use smart value, list filtering and math expressions.  I encourage you to review those linked sources to help learn more about rule writing.

For example of an approach to solve this:

{{#=}}ROUND( ( 0{{#lookupIssues}}{{#if(equals(status.statusCategory.name,"Done"))}}+{{Story points|0}}{{/}}{{/}} ) / {{lookupIssues.Story points.sum|0}} * 100, 0){{/}}

How this works:

• We want to divide the sum of the story points for completed issues by the total number of story points in the lookup results
• First we iterate over the lookup issues
• Filtering on the status category name; this allows handling multiple different status values with different names, but the same category
• For each one found, we add the story point value, with a default value of 0 in case the field is empty
• That value is wrapped in parentheses, again with a default value of 0 at the front
• We want to divide that sum by the overall sum story points, and that is {{lookupIssues.Story points.sum|0}} again adding a default value of 0
• Then we multiply by 100 to create the percentage
• This could produce a number with lots of decimal digits; to simplify we add the ROUND function to round up the next whole percentage value.  You can adjust this to the number of digits needed.

Kind regards,
Bill

[Rimsha Khan]

@Bill Sheboy Thank you so much for your help! I was able to use your example approach, and achieve the results I was looking for. I browsed sever atlassian community posts and found some helpful tips. Appreciate your quick reply, and detailed answer.

Kind regards, 

Rimsha

[Rimsha Khan]

@Bill Sheboy Hello!

I am facing a similar issue when trying to re-create at the feature level. I've tried a variety of changes and searched through the Atlassian Community.

The issue hierarchy is Feature > Epic > Story. I would like to see a roll up from the weighted epics at the feature level, using the epic percent completed I was previously calculating.

I created a field called Feature Percent Completed and used the modified the below expression, that you previously shared.

{{#=}}ROUND( ( 0{{#lookupIssues}}{{#if(equals(status.statusCategory.name,"Done"))}}+{{Epic Percent Completed |0}}{{/}}{{/}} ) / {{lookupIssues.Epic Percent Completed.sum|0}} * 100, 0){{/}}

Additionally, in my automation, I made the following changes:

I am getting an error with the "Linked Issue"

How can I have it run where it will look at the epics linked to the feature, and use the field "Epic percent completed" field on each epic to calculate the feature percentage complete?

Any guidance would be greatly appreciated!

[Bill Sheboy]

First thing, what does "percent completed" mean for your Feature issues?  That is...

If you only want an approximation based on the "percent completed" stored in the Epics, you could just average the values by adjusting your current rule:

• gather the Epics with lookup issues and JQL
• get the average value with {{lookupIssues.Epic Percent Completed.average}}

If you want it to reflect the value based upon the story points of the grandchild issues (i.e., Story) those values need to be evaluated directly.  Otherwise if one Epic has more issues (or Story Points) than another, it would not weight consistently.

To rollup up across the grandchild Stories, the rule could first use a call to lookup issues to identify the Epic children of the Feature, and then use that result to build another JQL to find the Story children for each Epic, and then calculate the value.

[Rimsha Khan]

@Bill Sheboy 

Thank you!

For the feature issues, percent completed is how you initially described it. I need it to calculate based on the "percent completed" field in the epics

Here is how I have updated the automation. I am getting a "No actions performed" status.

[Bill Sheboy]

If you want to update the feature, I believe the trigger / conditions should proceed if:

1. When the fields change in an Epic, update the Feature.  However your condition is checking for a Feature instead.  Try changing your condition to check for Epic.
2. Optional: if a user tampers with the values in the feature, you may want to recalculate the percentage.

For the primary case, you probably want something like this:

• trigger: Epic Percent Completed changes
• condition: issue type is Epic
• related issue condition: check if the Epic has a related Feature "parent"
• branch: to the Feature
  • action: lookup the issues for the sibling Epics
  • action: update the Feature with the average

Again please note, this will be less accurate due to differences in the individual Epics which are only summarized by their Epic Percent Completed field.

[Rimsha Khan]

@Bill Sheboy 

Here is what I have now, the below automation should update at all 3 levels. The first portion is working properly to update the epic percent completed field at the epic level. The second half is still not calculating/updating the Feature percent completed field at the feature level.

I've tried a variety of things, but nothing is getting it to populate at the feature level. 

[Bill Sheboy]

The parent link field is essentially sunset, replaced by the parent field consistently across the issue hierarchy: https://community.atlassian.com/t5/Jira-Software-articles/Introducing-the-new-Parent-field-in-company-managed-projects/ba-p/2377758

Please update your rule's JQL accordingly and re-test.

Next, have you confirmed you have the correct smart values for your fields?  Smart values are name, spacing, and case-sensitive.  When an incorrect smart value is used, it is replaced by null, and often does not lead to rule errors.

To confirm the smart values for your fields, please use the steps in this how-to article: https://support.atlassian.com/cloud-automation/docs/find-the-smart-value-for-a-field/

Finally, sometimes the audit log details are unclear what is happening in rule progress, particularly when the if / else blocks are used.  It may help to add specific writes to the log to confirm what is happening.

[Rimsha Khan · Accepted solution]

@Bill Sheboy Is this something you may be able to help with? Thank you!

[Kalyan Sattaluri]

Hello @Rimsha Khan 

Hope you are doing well.  Bill's formula worked for me and gave results as you wanted. Only thing is, if your field is called Story Points, then replace "Story points" with "Story Points" in his formula because field is case sensitive..

Also, Random suggestion that in your lookup JQL, maybe exclude issues which are cancelled just in case.

But yes, Please confirm the same and if everything looks good, please accept his answer so that future folks researching can benefit as its really awesome. Thanks!

[Rimsha Khan]

Hi @Kalyan Sattaluri ,

Thank you! I was able to test out Bill's formula as well, and had success. Appreciate the suggestions you shared too. I have accepted Bill's answer, and hoping others will benefit as well. 

Thank you and kind regards,

Rimsha