Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Removing outliers

Hi, I need some help to remove outliers from my data calculation. I have a MRO Material Stock list and the movimentation of that materials. I need to calculate the average and the max of lead time when buying that materials but I have some outliers in that data. 
that a data sample:

MaterialMaterial DescriptionQuantityBase Unit of MeasureAmount in LCStorage LocationPlantMovement TypePosting DateDocument DateLeadtime
54153386ELETRODO OK 46, ASME SFA5.1E6013 2,5MM18KG785,728001S22610120/01/202114/01/20216
54153386ELETRODO OK 46, ASME SFA5.1E6013 2,5MM18KG715,118001S22610129/10/202002/10/202027
54153386ELETRODO OK 46, ASME SFA5.1E6013 2,5MM20KG794,578001S22610129/10/202002/10/202027
54153386ELETRODO OK 46, ASME SFA5.1E6013 2,5MM-20KG-794,578001S22610229/10/202002/10/202027
54153386ELETRODO OK 46, ASME SFA5.1E6013 2,5MM-2KG-79,468001S22610228/10/202028/10/20200
54153386ELETRODO OK 46, ASME SFA5.1E6013 2,5MM20KG794,578001S22610128/10/202002/10/202026
54153386ELETRODO OK 46, ASME SFA5.1E6013 2,5MM18KG715,118001S22610128/10/202002/10/202026
54153386ELETRODO OK 46, ASME SFA5.1E6013 2,5MM18KG715,118001S22610128/10/202002/10/202026
54153386ELETRODO OK 46, ASME SFA5.1E6013 2,5MM-20KG-794,578001S22610228/10/202002/10/202026
54153386ELETRODO OK 46, ASME SFA5.1E6013 2,5MM-18KG-715,118001S22610228/10/202002/10/202026
54153386ELETRODO OK 46, ASME SFA5.1E6013 2,5MM2KG79,468001S22610128/10/202028/10/20200
54153386ELETRODO OK 46, ASME SFA5.1E6013 2,5MM-18KG-715,118001S22610228/10/202002/10/202026
54153386ELETRODO OK 46, ASME SFA5.1E6013 2,5MM-18KG-715,118001S22610224/10/202002/10/202022
54153386ELETRODO OK 46, ASME SFA5.1E6013 2,5MM18KG715,118001S22610124/10/202002/10/202022
54153386ELETRODO OK 46, ASME SFA5.1E6013 2,5MM-18KG-714,998001S22610208/10/202002/10/20206
54153386ELETRODO OK 46, ASME SFA5.1E6013 2,5MM18KG714,998001S22610108/10/202002/10/20206
54153386ELETRODO OK 46, ASME SFA5.1E6013 2,5MM15KG330,908001S22610123/07/202022/01/2020183
54152691CORREIA 3VX 0425 GOODYEAR8PC203,288001S22610122/07/202011/12/2019224
54153386ELETRODO OK 46, ASME SFA5.1E6013 2,5MM18KG643,608001S22610108/07/202029/06/20209
54153386ELETRODO OK 46, ASME SFA5.1E6013 2,5MM18KG607,998001S22610114/04/202009/03/202036
54153386ELETRODO OK 46, ASME SFA5.1E6013 2,5MM-0,500KG-11,818001S22620113/04/202013/04/20200
54153386ELETRODO OK 46, ASME SFA5.1E6013 2,5MM-1KG-23,628001S22620113/04/202013/04/20200
54152691CORREIA 3VX 0425 GOODYEAR4PC38,488001S22610108/04/202020/03/202019
54153386ELETRODO OK 46, ASME SFA5.1E6013 2,5MM5KG110,308001S22610108/04/202017/03/202022
54153386ELETRODO OK 46, ASME SFA5.1E6013 2,5MM18KG432,888001S22610108/04/202017/03/202022
54152691CORREIA 3VX 0425 GOODYEAR40PC388,058001S22610107/04/202025/03/202013
54153386ELETRODO OK 46, ASME SFA5.1E6013 2,5MM5KG100,108001S22610113/12/201905/12/20198
54152691CORREIA 3VX 0425 GOODYEAR20PC193,928001S22610127/11/201914/11/201913
54152691CORREIA 3VX 0425 GOODYEAR16PC319,448001S22610106/11/201919/09/201948
54152691CORREIA 3VX 0425 GOODYEAR20PC193,968001S22610123/09/201921/08/201933
54152691CORREIA 3VX 0425 GOODYEAR20PC193,968001S22610119/09/201921/08/201929
54152691CORREIA 3VX 0425 GOODYEAR-20PC-193,968001S22610219/09/201921/08/201929
54152691CORREIA 3VX 0425 GOODYEAR20PC193,968001S22610111/09/201921/08/201921
54152691CORREIA 3VX 0425 GOODYEAR-20PC-193,968001S22610211/09/201921/08/201921
54153386ELETRODO OK 46, ASME SFA5.1E6013 2,5MM15KG299,558001S22610129/08/201914/08/201915
54153386ELETRODO OK 46, ASME SFA5.1E6013 2,5MM20KG532,008001S22610103/05/201925/04/20198
54153386ELETRODO OK 46, ASME SFA5.1E6013 2,5MM-1KG-20,698001S22620101/03/201901/03/20190

I have tryed that solution: 

 

 

 

NOOUTMAX leadtime = 
var q1 = CALCULATE(
    PERCENTILEX.INC(MB51, MB51[Leadtime], .25),
    ALLEXCEPT(MB51, MB51[Material]))
var q3 = CALCULATE(
    PERCENTILEX.INC(MB51, MB51[Leadtime], .75),
    ALLEXCEPT(MB51, MB51[Material]))
var iqr = q3-q1
var upperpec = q3+iqr*1.5
return 
    CALCULATE(
       MAX(MB51[Leadtime]),
       FILTER(ALL(MB51), MB51[Leadtime]>0 && MB51[Leadtime]<= upperpec))

 

 

 

 

But I get to type of "errors". One when my data relationship is setted MB51 many*:one Stock list. The erros is, all my calculated column is blank
The another erro is when my data relationship  is setted MB51 many*:many* Stock list. The error is: some lines is well calculated and anothers is blank. Like the print above:

 

how to solve that error or another solution to remove outliers from my calculation. 

Thanks in advance

 

 

2 Replies