How can I extract percentile

Hi,

I need to get the percentile from issues cycle time and I’ve used the following formula to solve that:

-- annotations.group=Percentile
Percentile(
  VisibleRowsSet(),
  [Measures].[Product Cycle Time],
  50
)

The problem is that now I want a Gauge chart to show that value and now I can’t use VisibleRowSet() function. I’ve tried to replace that by a filter but I couldnt get issues from selected dimensions.

Percentile(Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    DateInPeriod(
      [Issue].CurrentHierarchyMember.Get('Resolved at'),
      [Time].CurrentHierarchyMember)
  ),
  [Measures].[Product Cycle Time],
  50
)

I just want to get a set of issues filtered by selected issue types, status and time on dimension.

Could you help me?

Thank you.

Best regards,
Ricardo.

Function VisibleRowSet() is applicable to cases when you would like to apply a formula to each row you are using in the report.

If you would like to have only one Rows representing all issues based on Pages criteria, the
VisibleRowSet() will give you one totaling member that might not work as expected.

The approach using function over Descendants of Issue is the correct one for this case. Function Descendants over Issues picks all issues, ignoring any dimension in the report selection, except Issue dimension. You would like to use some measure for filtering data there. You are using Issue property Issue resolution date. It filters out issues resolved in a period and therefore the filter applies to Time dimension only.

You are using custom measure Product Cycle Time in the formula as well. You would like to check what measures or properties are used for Product Cycle Time. If you are using measures related to different time period then they might not give you any data in results or it might not filter out the correct set of issues. If they are using issue properties only, it might not filter correct issues for a calculation.

You can add another filter by measure Issues resolved. If the Product Cycle Time uses some historical measures and could have values in another time period than issue resolution period, you can use this measure in a tuple with time default member:

Percentile(Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    DateInPeriod(
      [Issue].CurrentHierarchyMember.Get('Resolved at'),
      [Time].CurrentHierarchyMember)
  ),
  CASE WHEN [Measures].[Issues resolved] > 0 THEN
 ( [Measures].[Product Cycle Time] ,
  [Time].CurrentHierarchy.DefaultMember)
END,
  50
)

Daina / support@eazybi.com