Charting Story Points for Cross-functional Teams

[Oliver_Monaco]

Hi folks,

We have certain teams (eg. Backend) that complete tickets within their own Jira Projects during a Sprint, but also complete tickets for other teams’ Jira Projects based on whether a certain component (eg. ‘Backend’) is added to a ticket. All teams have the same sprint start and end dates, regardless of what Jira Project their tickets are stored. I am looking to put together a report that displays the cumulative story points completed for completed sprints. I created the measure below to return the number of story points that are ‘Done’ at the close of a sprint for tickets that are in the Jira project Backend, and tickets that have a component backend.

Sum(Filter(
Descendants([Project].CurrentMember, [Project].[Project]),
[Project].CurrentHierarchyMember.Name = 'Backend'),
(([Measures].[Sprint Story Points at closing],
[Transition Status.Category].[Done]))) +
Sum(Filter(
Descendants([Project].[Component].CurrentMember, [Project].[Component]),
[Project].[Component].CurrentHierarchyMember.Name MATCHES '^Back.*'),
([Measures].[Sprint Story Points at closing],
[Transition Status.Category].[Done]))

After using Pages to identify the close date of each sprint I end up with:

This gives me all the story points I need, but as I understand it I need to use the Time dimension rather than the Sprint dimension in Rows. This is due to the fact that tickets resulting from this measure span multiple Sprints and Projects. So, I can’t clearly convey the sprint that each bar represents to other teams less familiar with sprint start and end dates.

If I could somehow replace the ‘W33, Aug 12 2019’ label with ‘Q3 Sprint 4’ that would clearly convey the sprint that the Backend team completed work in. Alternatively, if having the sprint (or sprints) on the x-axis isn’t possible, I may be fine with having a stacked bar chart displaying the different Jira Projects’ Sprints (eg. iOS, Android, Web) that the Backend team completed work in. Can someone weigh in on this?

Separate but related, I believe my filter could be improved to better match the below JQL query.

project = Backend OR component = Backend

In my current measure, if a ticket were in the project ‘Backend’ and for some reason had the component ‘Backend’ the ticket’s story points would be double counted. What I should be using is MDX’s version of an ‘OR’ statement somehow. Any feedback on that would be welcomed!

[Oliver_Monaco]

Regarding filter measure, I believe the below MDX statement improves upon my previous measure, but I am open to improvements.

Sum(Filter(
[Project].Members,
[Project].CurrentHierarchyMember.Name = 'Backend' OR 
[Project].[Component].CurrentHierarchyMember.Name MATCHES '^(?i)Back.*'),
([Measures].[Sprint Story Points at closing],
[Transition Status.Category].[Done]))

[daina.tupule]

eazyBI does not have an option to override dimension member names using any other attributes, different dimension names, or any other calculation.

However, I hear you on this one. An option to better represent parallel Sprints for related Teams would be a great improvement. We have some ideas on this in our backlog. I will add a community vote to it.

You also asked how to avoid double counting when you have a project and component with the same name:

JIRA JQL works on Issues and therefore, it will pull in an issue if it either has Project or Component with some name with this JQL: project = Backend OR component = Backend

A project with the name Backend and a component with the name Backend are two different members in the Project dimension in eazyBI. eazyBI counts them separately. Both project and component members will be pulled in with the both formulas and you will get results with double-counted values.

I would suggest checking if a component is not a part of the project with the same name and count in component only it is not a part of the Backend project:

Sum(Filter(
    Descendants([Project].CurrentMember, [Project].[Project]),
    [Project].CurrentMember.Name = 'Backend'),
    ([Measures].[Sprint Story Points at closing],
    [Transition Status.Category].[Done]))
+    
Sum(Filter(
    Descendants([Project].CurrentMember, [Project].[Component]),
    [Project].CurrentMember.Name MATCHES '^Back.*'
    AND 
    [Project].CurrentMember.Parent.Name <> 'Backend'
    ),
   ([Measures].[Sprint Story Points at closing],
   [Transition Status.Category].[Done]))

Daina / support@eazybi.com

[Oliver_Monaco]

Thanks for your response Daina. It is encouraging to hear that EazyBI is considering developing functions to represent parallel Sprints.

Also, thank you for providing the query, however I just want to make sure I understand it fully. It seems that functionally it is the same as the MDX statement I commented on Oct 17th. Are there functional differences between your query and mine from my comment on the 17th that you can highlight that I’m not understanding? I want to be sure I’m implementing the measure that best reflects my business need. It is below as well.

Sum(Filter(
[Project].Members,
[Project].CurrentHierarchyMember.Name = 'Backend' OR 
[Project].[Component].CurrentHierarchyMember.Name MATCHES '^(?i)Back.*'),
([Measures].[Sprint Story Points at closing],
[Transition Status.Category].[Done]))

Thanks again!

[daina.tupule]

Both codes are quite similar. In the formula I shared with you, I addressed members on level Project and Component explicitly and used a bit different filters on them.

I used an additional filter to exclude components from the project name with the Backend. I used this filter, to avoid double-counting for those components.

The initial formula might include the whole project with the name Backed and, in addition, might count in components from this project, if the component matches the filter criteria.

If projects with the name Backed do not have a component with the name starting with Back this is not the problem.

You can use this simpler code then:

Sum(Filter(
[Project].Members,
[Project].CurrentHierarchyMember.Name MATCHES '^(?i)Back.*'),
([Measures].[Sprint Story Points at closing],
[Transition Status.Category].[Done]))

eazyBI ignores a level with the CurrentHierarchyMember.
eazyBI will apply the formula both for project and component level members when you have not specified level for a set ( [Project].Members pulls in any level of default project hierarchy)

this
[Project].[Component].CurrentHierarchyMember.Name

will work like this:
[Project].CurrentHierarchyMember.Name

Daina / support@eazybi.com

[daina.tupule]

eazyBI included the option to build custom hierarchies based on additionally imported properties for sprint dimension with version 6.1.
Here are two community posts with examples on how to use this option to build hierarchies by sprint start date (weekly) or by sprint naming pattern:

Sprint date (weekly):

Aggregate Sprint By Name

Here is an example of REST API that would import Week cycle as Sprint property. This Sprint property will pick the Sprint start date and show it to the Monday of that week, and import the property with this pattern: %yW%U, %b %d %Y, for example, 20W39, Sep 28 2020 - year, week number, and week start date) To use this code: In Source Data (where you already have Jira as a data source), select “Add new source application”. Chose the option to “import definition” and paste in the code below…

Sprint naming pattern:

Combining different teams sprints into one

With version 6.1 we added a new to create custom hierarchies in some dimensions, including Sprints. You would like to import custom properties for sprints to represent a potential grouping mechanism. For example, you can import a property cycle for Sprints with additional data import. Then you can use this custom property Cycle to build a new custom hierarchy in Sprint dimension and group sprints by cycles dynamically. Import additional property to Sprint based on Sprint naming pattern XX Spr…

With version 6.4 we added a default Sprint hierarchy by Sprint status. It allows using this dimension to work with the currently active Sprint or use more efficient filtering for issues closed in any sprint.

Daina / support@eazybi.com