Calculated Measure for AVG of Sub-Bugs per issue type

Questions & Answers
1

Hello,
I need to count the average of Sub-Bugs (Issue Type Name) per two issue types: Story and Task, and also per Team. Could you please help me?

2

Hi @Elena_Kazarina

Using this formula, you could try creating a new calcualted measure with the decimal output format.

[Measures].[Sub-tasks created]
/
[Measures].[Issues created]

FYI, “Sub-tasks created” measure would count all sub-tasks under issue.

If there are different sub-task types in your Stories and Tasks and you want to count just one kind (Sub-Bug), you might want to consider using this formula (but it is slower to run) for your calculated measure:

Nonzero(Sum(
Filter(
DescendantsSet([Issue.Sub-task].CurrentMember,[Issue.Sub-task].[Parent]),
[Measures].[Issues created]>0
),
  Count(
  Filter(
    ChildrenSet(
      [Issue.Sub-task].CurrentMember
    ),
    [Measures].[Issue type] = "Sub-Bug"
  )
  )
)
/
[Measures].[Issues created]
)

Martins / eazyBI staff

image
image1684Ă—948 91.5 KB

1 Like
3

@martins.vanags (Profile - martins.vanags - eazyBI Community)Thank you so much for the answer, Martins! The thing is, that still doesn’t work for me. So basically, I understand that not every Story has a sub-bug. I tried so many ways to count the average amount of sub-bugs per story correctly, and even though I succeeded in counting it, I couldn’t properly insert the restriction to count only completed stories with resolution = Done and sub-bugs in status Closed (Not cancelled). I need it to be counted per the custom field “Team”. For some reason, the formula you suggested wouldn’t show me any results.

image
image1502Ă—567 114 KB

4

This is my closest try, but I still cannot apply filters for the resolution of stories, and the status of sub-bugs. I tried to put “Status” to Pages and apply the filter, but then my custom measure for average of Sub-Bugs showed nothing. Our final and valid status for story is “Completed” and for Sub-bugs is “Closed”

image
image1224Ă—681 90.8 KB

image
image1012Ă—761 86.5 KB

5

Hi,
Check this line in the code:

[Measures].[Issue type] = "Sub-Bug"

From your screenshots it seems you have an issue type “Sub-bug” not “Sub-Bug”
This line is case sensitive.

Try this code

Nonzero(Sum(
Filter(
DescendantsSet([Issue.Sub-task].CurrentMember,[Issue.Sub-task].[Parent]),
[Measures].[Issue type] = "Story"
AND
[Measures].[Issue resolution] = "Done"
AND
[Measures].[Issues resolved]>0
),
  Count(
  Filter(
    ChildrenSet(
      [Issue.Sub-task].CurrentMember
    ),
    [Measures].[Issue type] = "Sub-bug"
    AND
    [Measures].[Issue status] = "Closed"
  )
  )
)
/
[Measures].[Issues resolved]
)

Martins / eazyBI