# Histogram with Percentiles, Mode, Mean and Median

Hi,

I would like to create a Histogram with Percentiles, Mode, Mean and Median.
I used the forum thread below to create the histogram:

I tried to use the topic below to create the Percentiles, but it didn’t work, as shown in the image:

The correct value of the 50th percentile is 5.

Could you please send the formula you use for calculating the percentile?

Lauma / support@eazybi.com

Here’s the formula:

Cache(NonZero(Percentile(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
DateInPeriod(
[Measures].[Issue resolution date],
[Time].CurrentHierarchyMember
)),
CASE WHEN [Measures].[Issues resolved] > 0
THEN
NonZero(([Measures].[Days in transition status],
[Status.Category].[Done],
[Time].CurrentHierarchy.DefaultMember))
END
, 50 )
))

I’m sorry, I can’t see where the problem could be quite yet. Could you please send exported report definition (Create reports) to support@eazybi.com? Add the link to this community post and notify that you discussed this with Lauma.

Lauma / support@eazybi.com

Hey @Kleber_Rocha and @lauma.cirule ,

If so, can you share the solution, please?

Thank you!

Hi @Ciba ,

The main issue was that the original formula here CASE logic returning unexpected results for Monte Carlo chart percentile line draws the Percentile line if the value falls in the Resolution interval range, but @Kleber_Rocha was using interval dimension with one value range (e.g., 45 - 45). Here is an example formula that would work in such a case:

``````CASE WHEN
-- left part of the range is equal to the percentile…
-- extract the first element of the range using regex
Val(ExtractString(
[Resolution interval].CurrentMember.Name,
"(\d+) - (\d+)",1)
) =
Round(Percentile(
Filter(
Descendants([Issue].CurrentMember,[Issue].[Issue]),
([Measures].[Issues resolved],
[Resolution interval].DefaultMember
) > 0
),
([Measures].[Total resolution days],
[Resolution interval].DefaultMember
), 50
))
THEN
--print the percentile at the range
"50th percentile"
END
``````

Lauma / support@eazybi.com

1 Like