Calculate the average of the 90th percentile of a custom measure

Questions & Answers
1

We use a calculated measure called ‘Cycle Time’ which calculates the time from when a ticket first moves into an In Progress status category to Done category.
I want to calculate the average cycle time of all tickets up to the 90th percentile to eliminate outliers.

2

Hi @Ruchi ,

You may want to explore the Percentile() function - Percentile - eazyBI. The formula for your particular use-case could look similar to the one below:

Percentile(
  Filter(
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
    -- first transition to the category "Done" in the current Time dimension period
    DateInPeriod(
      (
        [Measures].[Transition to status first date],
        [Transition Status.Category].[Done],
        [Time].CurrentHierarchy.DefaultMember
      ),
      [Time].CurrentHierarchyMember
    )
    AND
    -- has a transition out of category "In Progress"    
    ([Measures].[Transitions from status],
    [Transition Status.Category].[In Progress],
    [Time].CurrentHierarchy.DefaultMember) > 0
  ),
  -- number of days spent in category "In Progress"
  ([Measures].[Days in transition status],
  [Transition Status.Category].[In Progress],
  [Time].CurrentHierarchy.DefaultMember),
  -- 90th percentile
  90
)

Best,
Roberts // support@eazybi.com