Calculate 95% of Orders for Avg LeadTime in Pivot Table

[Tanish]

Hello Experts,

Need your suggestion on issue i'm stuck in.

I need to calculate the Avg Leadtime for 95% of the order only after sorting that order in Ascending order, so that some order which is taking extra time can be excluded while calculating the Avg Leadtime. In other removing of Outliers.

I used Fractile function to get the output.
Sum({<TOTAL_L_TIME={"<=$(=Fractile(TOTAL_L_TIME, .95))"}>} TOTAL_L_TIME)

 / Sum({< TOTAL_L_TIME={"<=$(=Fractile(TOTAL_L_TIME, .95))"}>} LENS_COUNT_COMP)

But this expression is not working properly in the Pivot table because i think it is considering whole date range while calculating the 95% not the specific date.

This is my Table Structure, Now i also have a buttons at top to see the table in Week, Month and Day wise.

Currently it is not showing correct result but if i select any specific date or specific week from the table then it show correct value.

Need your input, need to finish this Task soon.

Thanks in Advance...

[F_B]

Hi @Tanish ,

you can use an aggregation function within a set analysis to dynamically adjust the context of the Fractile function based on the current selection in the pivot table. First create a variable to store the 95th percentile value, this variable dynamically adjusts based on the current context. Then use this variable within the set analysis, filter the data to include only the values below this 95th percentile value.

Hope this can help you