Filtering values within 10th & 90th percentile

[Enforcer274]

Hi, I am trying to remove outliers from by data set using any data between the 10th and 90th percentile. And get the average of the new filtered dataset.

Data

PN In: Part Number

Reference: Specific order number (Subcategory to PN)

Count Parts: Number of PN
RTAT: amount of time we take to complete the part,

          +ve RTAT is within limits, -ve RTAT means were behind schedule

10%RTAT: 10th percentile of RTAT

10%RTAT = PERCENTILE.INC(WS[RTAT],0.1)

90%RTAT: 90th percentile of RTAT

90%RTAT = PERCENTILE.INC(WS[RTAT],0.9)

FilterRTAT: Using 10% & 90% RTAT to get value within range and make value out of range = 0

I found that this does not work as it take the individual 10% & 90% of each reference number and us it in the if statement which results to everything is within limits. As shown below

FilterRTAT = IF(WS[RTAT]<=[90%RTAT] && WS[RTAT]>=[10%RTAT],WS[RTAT],BLANK())

Fig 1

FilterRTAT V2: I found this formula online. It is basically the same thing but it uses the total 10% & 90% of the whole dataset in the if statement. As shown below, for PN /2687A...., -473.40 & 13.00 is taken as the filter value for FilterRTAT V2, which is not what I want.

So as an example, I want PN /2687A.... to be filtered --> anything between -700 & -86.40 to remain the same and anything outside that range to be blank() or 0. the above value is shown in green in Fig 3.

And same for the different PN with thier own respective 10th & 90th percentile range.

FilterRTAT V2 = 
VAR Q4RTAT =
    PERCENTILE.INC(WS[RTAT],0.9)
RETURN 
VAR Q1RTAT =
    PERCENTILE.INC(WS[RTAT],0.1)
RETURN
IF(WS[RTAT]<=Q4RTAT && WS[RTAT]>=Q1RTAT,WS[RTAT],BLANK())

Fig 2Fig 3

This is an overview of my dashboard

Fig 4

Question: So does anyone know how to filter values between 10 & 90 percentile of different categories (PN in) within a dataset. Or assign 10 & 90 percentile to different categories based on the calculations. If yall need any more dataset or examples, Im able to provide.

[lbendlin]

Great question. Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. 

[Enforcer274]

Sorry for the late reply, i dont really know how to share datasets. But heres the link for my PowerBI file. https://app.powerbi.com/links/HXInX4gsO5?ctid=7604ff02-abd8-45db-8cac-550054323fc9&pbi_source=linkSh...