Histogram with Percentiles, Mode, Mean and Median

[Kleber_Rocha]

Hi,

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

Downstream Cycle Time Histogram

Good morning guys. My first question here on the forum. I would like to build a Histogram with the Cycle Time of my Downstream Stream. This report will have the x axis with the days of the Cycle Time and the y axis will have the issue number with the same Cycle Time. We will have a variable with the downstream flow. I tried to use Age interval and Resolution interval dimension, but it was not possible as it adds the opening date to the resolution date. Can anyone help me?

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

CASE logic returning unexpected results for Monte Carlo chart percentile line

Hello, I am trying to build a Monte Carlo style chart based off of Resolution Interval dimension and using Issues resolved measure. It mostly works, but I get an unexpected result. I am trying to show a vertical line at the range matching 90th percentile. I am getting 2 hits on my CASE, when I expect only one. For reference, my 90th percentile value is 124 so I expect the line to land between 120 - 129 range. [image] CASE WHEN – left part of range is less than percentile… – extract the firs…

The correct value of the 50th percentile is 5.

[lauma.cirule]

Hi @Kleber_Rocha,

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

Lauma / support@eazybi.com

[Kleber_Rocha]

Hi @lauma.cirule,

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],
[Transition Status].[Fluxo Customer lead time],
[Status.Category].[Done],
[Time].CurrentHierarchy.DefaultMember))
END
, 50 )
))

[lauma.cirule]

Hi @Kleber_Rocha,

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

[Ciba]

Hey @Kleber_Rocha and @lauma.cirule ,

Was this issue resolved?

If so, can you share the solution, please?

Thank you!

[lauma.cirule]

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