85th percentile - is not calculating correctly

[miriam]

Hi,
We are trying to calculate the 85th percentile of the lead time and cycle time, but it seems that the results are not correct.
In the example below, in easybi the 85th percentile is resulting in 514 days when it should be 580 days.

The measure used is:
– annotations.group=Percentile
Cache(
Percentile(
Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
DateBetween(
[Issue].CurrentHierarchyMember.Get(‘Created at’),
DateParse(‘Dec 2018’),
DateParse(‘Now’)
) AND
DateBetween(
[Issue].CurrentHierarchyMember.Get(‘Resolved at’),
DateParse(‘90 days ago’),
DateParse(‘Now’)
)
AND [Measures].[Issues resolved] > 0),
[Measures].[Initiative Cycle Time],
85
)
)

Can you help us?
Thank you

[janis.plume]

Hi,

The usual way to debug such calculations is to check the set of members (issues) from which the percentile is calculated, and the values used.

For instance, the following formula prints the list of all the issues used in your formula of percentile calculation:

SetToStr(
    Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
DateBetween(
[Issue].CurrentHierarchyMember.Get('Created at'),
DateParse('Dec 2018'),
DateParse('Now')
) AND
DateBetween(
[Issue].CurrentHierarchyMember.Get('Resolved at'),
DateParse('90 days ago'),
DateParse('Now')
)
)
)

The result of the percentile is generated by the standard function of the MDX engine. The following formula will generate the list of values used for the percentile calculation:

Generate(
  Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
DateBetween(
[Issue].CurrentHierarchyMember.Get('Created at'),
DateParse('Dec 2018'),
DateParse('Now')
) AND
DateBetween(
[Issue].CurrentHierarchyMember.Get('Resolved at'),
DateParse('90 days ago'),
DateParse('Now')
)
),
Format([Measures].[Total resolution days],"###0.0#"),
CHR(10)
)

You may use them in another tool to check if the MDX engine is working identically.

Kindly,
Janis, eazyBI support