Calculate percentile from custom measure only for visible values in the table

Hi!

I have taken the Cycle Time Scatterplot (here), which has a custom measure called ‘Cycle days in progress’, which in turn uses “Average days in progress”:

-- the formula calculates total time spent in cycle for resolved issues. 
-- should use in report or in another formula with Transition status dimension
IIF(
  [Time].CurrentHierarchyMember is [Time].CurrentHierarchy.DefaultMember
  AND
  [Issue].CurrentMember.Level.Name <> "Issue",
    -- with time default memeber total days in status / distinct count of issues moving from the status for currently resolved issues only
    ([Measures].[Days in transition status],
      [Status.Category].[Done]) 
    /
    ([Measures].[Transitions from status issues count] ,
    [Status.Category].[Done]),
  -- calculation for a selected period/timeline requires issue level calculation. 
  NonZero(AVG(
    Filter(
      Descendants([Issue].CurrentMember, [Issue].[Issue]),
      -- filter issues resolved in period
      DateInPeriod(
        [Measures].[Issue resolution date],
        [Time].CurrentHierarchyMember
    )),
    CASE WHEN 
     -- filter currently resolved issues by any dimension, except transition status
      ([Measures].[Issues resolved], [Transition Status].CurrentHierarchy.DefaultMember) > 0
    THEN
    -- count total time spent for this issue in a particular status - use Transition status in report 
      NonZero(
        DefaultContext((
         [Measures].[Days in transition status],
         [Issue].CurrentMember,
         [Transition Status].CurrentHierarchyMember, -- use Transition status in report or in another formula
         [Status.Category].[Done]))
      )
    END
  ))
)

This generates the scatter plot correctly and I can filter the Time dimension to the suitable one (say the last month, for example).

However, I’m trying to calculate the percentile (let’s say 75th) on the visible data in the table that generates the chart and then add that percent line as an horizontal line. The output is something like this.

I’ve been combing through this community and found the threads:

  • How can I extract percentile
  • How can I get 85th percentile for cycle and lead time
  • Calculate the average of the 90th percentile of a custom measure
  • Histogram with Percentiles, Mode, Mean and Median

But none of them worked.

Anyone out there that can please help on this?

Thank you!

1 Like