How to calculate average number of status changes of closed tickets?

Hi,
I would like to create a report showing the average number of status changes of closed tickets by month. I found this post from 2020 which is heading towards a solution, but it only counts the status changes in the given month, not the status changes in previous months. I am - again - at a loss on how to move forward.

Hi, @Maiko

Welcome to the eazyBI community.

When the Time dimension is selected for the given month, and you are looking for some aspects for “all times” - please use the [Time].DefaultContext to ignore the Time set in the Page filters.

NonZero(
  Avg(
    Filter(
      Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
      (
        [Measures].[Issues closed],
        [Time].CurrentMember
      ) > 0
    ),
    (
      [Measures].[Transitions to status],
      [Time].CurrentHierarchy.DefaultMember
    )
  )
)

This formula does the following:

  1. Filters issues using [Measures].[Issues closed] with [Time].CurrentMember to select only issues closed in the current month (as selected in pages).
  2. For the filtered issues, calculate the average number of transitions using [Measures].[Transitions to status] with [Time].CurrentHierarchy.DefaultMember to count all transitions regardless of when they occurred.

Kindly,
Ilze

1 Like