Multiple Fix Version leads to double counting

Questions & Answers
1

Hello again.

So, I’ve created a report with “Fix Version” (Rows) and “Measures” (Columns). I’ve created a calculated member to aggregate a number of Fix Versions (e.g. APRIL-SAP, APRIL-SFDC, etc). I achieved this using the following:

Aggregate({
[Fix Version.By name].[DBT2019-FEB-ALL],
[Fix Version.By name].[DBT2019-FEB-SAP],
[Fix Version.By name].[DBT2019-20FEB-THDRHEAD],
[Fix Version.By name].[DBT2019-27FEB-THDRHEAD]})

For the Measures, I have some custom measures as well, to aggregate all Story points with certain Statuses (and the same for the number of Stories). Achieved this way:

Aggregate({
[Status].[Done],
[Status].[In Release Preparation],
[Status].[Ready for Deployment],
[Status].[Closed]},
[Measures].[Story Points created])

AND

Aggregate({
[Status].[Done],
[Status].[In Release Preparation],
[Status].[Ready for Deployment],
[Status].[Closed]},
[Measures].[Issues created])

Now…some Stories have more than one Fix Version populated and so I’m getting a double count. Is there a way to just could to avoid this (by somehow looking at the Story ID or something and de-dup)?

2

Hi,

Since “Fix Version” is dimension created from Jira releases (which is a multi-value field in Jira) there could be double counting in your specific report layout if one issue is assigned to more than 1 version from the Aggregated Fix Version set.

You could quite easily fix the calculation for “Issues created” by replacing it with a distinct measure “Issues created count”, however, there is no such substitute for “Story points created”.
This calculation would become more complex but it is possible to calculate your expected results anyway.

Try these codes instead:

Aggregate({
[Status].[Done],
[Status].[In Release Preparation],
[Status].[Ready for Deployment],
[Status].[Closed]},
[Measures].[Issues created count])

AND for Story points the following:

NonZero(
SUM(
Filter(
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
Aggregate({
[Status].[Done],
[Status].[In Release Preparation],
[Status].[Ready for Deployment],
[Status].[Closed]},
[Measures].[Issues created])>0
),
[Measures].[Story Points created]
)
)

Please make sure you turn on the “Nonempty” cross-join button for dimension added to rows.

Martins / eazyBI support

3

Awesome, will try that out, thanks @martins.vanags!

4

Hello, I’d like to create a similar hours spent measure that removes the double counting if there are issues with multiple fixed versions. Currently, when I would like to report the number of hours spent specific fixedVersions, but due to some issues with multiple fixed versions, those issues are double counted so the hours spent are actually more than they should be. Can you please help me with the code for this?

5

Hi @sammian

Try using the “Hours spent” measure for the formula

NonZero(
SUM(
Filter(
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
Aggregate({
[Status].[Done],
[Status].[In Release Preparation],
[Status].[Ready for Deployment],
[Status].[Closed]},
[Measures].[Issues created])>0
),
[Measures].[Hours spent]
)
)

Now each issue should be counted just once

Martins / eazyBI support

6

I have the same issue with fix versions being double counted.

In my use case I am trying to determine the open issues.
I am using the Issues History measure and Transition Status Dimension to find the open issues

[Transition Status].[Transitions Unresolved]
Aggregate
({
[Transition Status].[Open],
[Transition Status].[In Progress],
[Transition Status].[In Review],
[Transition Status].[Blocked Needs Info]
})

How can I eliminate the double counted issues because they have more than one fix version assigned to them

7

@jgibson

You would need to use a custom calculated measure instead of “Issues history”

NonZero(
Count(
Filter(
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
(
[Measures].[Issues history],
[Transition Status].[Transitions Unresolved]
)>0
)
)
)

Don’t forget to enable “Nonempty” cross join to optimize the calculation steps for this measure.

Martins / eazyBI support