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

Hi Daina,

I have a question regarding the Descendants function here. So the first part of Descendants function is the current member, and the second part is the level you would like to return regarding the current member. So does it mean the descendant (the child) of the ‘currentMember’ level would be [issue].[issue]? However, I think the level [issue].[issue] should be the parent level of [currentMember] instead of the child level. Last but not least, what will the query ‘‘Descendants([Issue].CurrentMember, [Issue].[Issue])’’ return? Does it return the set of a member in [issue].[issue] level? Thank you so much.

Wenhao

You are correctly describing how Descendants function works. Descendants address the Current member of Issue dimension used/not used in report and will retrieve a set of issues on Issue level. It address the default hierarchy of issues.

If Issue dimension is not used in the report the the current member is default member and the formula will give you all issues imported into account ([Issue].[Issue].Members).

If you are expanding issues to Issue level in the report, then Descendants of issue on [issue].[issue] level is one issue itself.

This gives you a flexibility and performance when expanding to issue level or using drill through issues.

This is the most common, suggested way how to address issue level calculations. In some case, you would like to use descendants with other particular issue hierarchy (Issue.Epics, Issue.Sub-tasks). But you would like to use it for quite rare cases, when the default usage of descendants can’t give you required results.

Daina / support@eazybi.com

All clear! Thank you very much, Daina.

Wenhao