How to remove the top 5% of the Data to get the P95(AVG)

Question & Answer
, , ,
1

Hello Everyone
Frame: 95th percentile average can be calculated by taking the average of the values lesser than 289.615 (the 95th percentile value). ((8.1 + 8.316 + 8.618 +…+406.977 + 849.035)/48) = 92.254.

I am calculating the times between when a work order comes until the time this Work Order is executed.
I am using a calculated field, using the function datediff and is working, I managed to make a table with the sum of hours by office, the Median, but I would like to have the Average of the Bottom 95% of the Data (this is the standard measure).
I managed to do it with excel and manually extract the TOP 5%, but I would like to know if its possible to do it inside Quicksight, I tried to make a calculated field where I use an ifelse and compares to the P95, if its lower I use that data and if its higher would be null, but I can not use non-aggregated and aggregated data.

2

What is your current calculated field?

You would need to group your measurement comparison but it might look something like this.

ifelse(avg({Sales}) > percentile({Sales},30),'Over 30','Under 30')
3

@duncan my calculated field is a datediff in hours, in which I made a pivot table by site and by month or year

I would like to trim the data to the bottom 95%, so I would like to storage the values below that in the Percentil 95 and calculate the AVG of this new calculated field for each month/year

ifelse(avg({Sales}) > percentile({Sales},30),'Over 30','Under 30') (this will return me if a certain value is below or over the P95)
5

Hello @Luciano_Deon !

Are you still running into problems with this calculation or were you able to find a work around?

For percentile, you should be able to run percentile({Sales}, 95) to get the 95th percentile.

1 Like
6

@duncan , I do not need the P95, I need the avg of the 95% of the data.
For example, my population is 1 to 100, I need to remove the top 5, so will be 1 to 95, and to that sample do an AVG.

7

Hello @Luciano_Deon !

Can you try the following calculations:

Calc 1 = percentileContOver({Customers},95 ,[{Sales}], PRE_AGG)

  • One note here: I have made Sales my partition field so that I am saying "show customers in the 95th percentile by sales.

Calc 2 = avgOver(avg(Sales), [PercentileTestCalc1])

  • Here I am saying show me the avg sales of those customers that I partitioned above.

Let me know if this helps!

8

Hello @Luciano_Deon !

Are you still running into problems with this or was my comment above helpful? If it was could you mark it as a solution to help the community?

9

Hey @Luciano_Deon !

For now I am marking my comment as a solution but if you run into an issue with this in a future please feel free to post it at the top of the community!

10

For now is working, I did hours by region, and the number seems to be correct, and in the visual I need to also place the Aux calculated field, sorry for the late reply, I was super busy lately

1 Like