Forum Discussion
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:
| Material | Material Description | Quantity | Base Unit of Measure | Amount in LC | Storage Location | Plant | Movement Type | Posting Date | Document Date | Leadtime |
| 54153386 | ELETRODO OK 46, ASME SFA5.1E6013 2,5MM | 18 | KG | 785,72 | 8001 | S226 | 101 | 20/01/2021 | 14/01/2021 | 6 |
| 54153386 | ELETRODO OK 46, ASME SFA5.1E6013 2,5MM | 18 | KG | 715,11 | 8001 | S226 | 101 | 29/10/2020 | 02/10/2020 | 27 |
| 54153386 | ELETRODO OK 46, ASME SFA5.1E6013 2,5MM | 20 | KG | 794,57 | 8001 | S226 | 101 | 29/10/2020 | 02/10/2020 | 27 |
| 54153386 | ELETRODO OK 46, ASME SFA5.1E6013 2,5MM | -20 | KG | -794,57 | 8001 | S226 | 102 | 29/10/2020 | 02/10/2020 | 27 |
| 54153386 | ELETRODO OK 46, ASME SFA5.1E6013 2,5MM | -2 | KG | -79,46 | 8001 | S226 | 102 | 28/10/2020 | 28/10/2020 | 0 |
| 54153386 | ELETRODO OK 46, ASME SFA5.1E6013 2,5MM | 20 | KG | 794,57 | 8001 | S226 | 101 | 28/10/2020 | 02/10/2020 | 26 |
| 54153386 | ELETRODO OK 46, ASME SFA5.1E6013 2,5MM | 18 | KG | 715,11 | 8001 | S226 | 101 | 28/10/2020 | 02/10/2020 | 26 |
| 54153386 | ELETRODO OK 46, ASME SFA5.1E6013 2,5MM | 18 | KG | 715,11 | 8001 | S226 | 101 | 28/10/2020 | 02/10/2020 | 26 |
| 54153386 | ELETRODO OK 46, ASME SFA5.1E6013 2,5MM | -20 | KG | -794,57 | 8001 | S226 | 102 | 28/10/2020 | 02/10/2020 | 26 |
| 54153386 | ELETRODO OK 46, ASME SFA5.1E6013 2,5MM | -18 | KG | -715,11 | 8001 | S226 | 102 | 28/10/2020 | 02/10/2020 | 26 |
| 54153386 | ELETRODO OK 46, ASME SFA5.1E6013 2,5MM | 2 | KG | 79,46 | 8001 | S226 | 101 | 28/10/2020 | 28/10/2020 | 0 |
| 54153386 | ELETRODO OK 46, ASME SFA5.1E6013 2,5MM | -18 | KG | -715,11 | 8001 | S226 | 102 | 28/10/2020 | 02/10/2020 | 26 |
| 54153386 | ELETRODO OK 46, ASME SFA5.1E6013 2,5MM | -18 | KG | -715,11 | 8001 | S226 | 102 | 24/10/2020 | 02/10/2020 | 22 |
| 54153386 | ELETRODO OK 46, ASME SFA5.1E6013 2,5MM | 18 | KG | 715,11 | 8001 | S226 | 101 | 24/10/2020 | 02/10/2020 | 22 |
| 54153386 | ELETRODO OK 46, ASME SFA5.1E6013 2,5MM | -18 | KG | -714,99 | 8001 | S226 | 102 | 08/10/2020 | 02/10/2020 | 6 |
| 54153386 | ELETRODO OK 46, ASME SFA5.1E6013 2,5MM | 18 | KG | 714,99 | 8001 | S226 | 101 | 08/10/2020 | 02/10/2020 | 6 |
| 54153386 | ELETRODO OK 46, ASME SFA5.1E6013 2,5MM | 15 | KG | 330,90 | 8001 | S226 | 101 | 23/07/2020 | 22/01/2020 | 183 |
| 54152691 | CORREIA 3VX 0425 GOODYEAR | 8 | PC | 203,28 | 8001 | S226 | 101 | 22/07/2020 | 11/12/2019 | 224 |
| 54153386 | ELETRODO OK 46, ASME SFA5.1E6013 2,5MM | 18 | KG | 643,60 | 8001 | S226 | 101 | 08/07/2020 | 29/06/2020 | 9 |
| 54153386 | ELETRODO OK 46, ASME SFA5.1E6013 2,5MM | 18 | KG | 607,99 | 8001 | S226 | 101 | 14/04/2020 | 09/03/2020 | 36 |
| 54153386 | ELETRODO OK 46, ASME SFA5.1E6013 2,5MM | -0,500 | KG | -11,81 | 8001 | S226 | 201 | 13/04/2020 | 13/04/2020 | 0 |
| 54153386 | ELETRODO OK 46, ASME SFA5.1E6013 2,5MM | -1 | KG | -23,62 | 8001 | S226 | 201 | 13/04/2020 | 13/04/2020 | 0 |
| 54152691 | CORREIA 3VX 0425 GOODYEAR | 4 | PC | 38,48 | 8001 | S226 | 101 | 08/04/2020 | 20/03/2020 | 19 |
| 54153386 | ELETRODO OK 46, ASME SFA5.1E6013 2,5MM | 5 | KG | 110,30 | 8001 | S226 | 101 | 08/04/2020 | 17/03/2020 | 22 |
| 54153386 | ELETRODO OK 46, ASME SFA5.1E6013 2,5MM | 18 | KG | 432,88 | 8001 | S226 | 101 | 08/04/2020 | 17/03/2020 | 22 |
| 54152691 | CORREIA 3VX 0425 GOODYEAR | 40 | PC | 388,05 | 8001 | S226 | 101 | 07/04/2020 | 25/03/2020 | 13 |
| 54153386 | ELETRODO OK 46, ASME SFA5.1E6013 2,5MM | 5 | KG | 100,10 | 8001 | S226 | 101 | 13/12/2019 | 05/12/2019 | 8 |
| 54152691 | CORREIA 3VX 0425 GOODYEAR | 20 | PC | 193,92 | 8001 | S226 | 101 | 27/11/2019 | 14/11/2019 | 13 |
| 54152691 | CORREIA 3VX 0425 GOODYEAR | 16 | PC | 319,44 | 8001 | S226 | 101 | 06/11/2019 | 19/09/2019 | 48 |
| 54152691 | CORREIA 3VX 0425 GOODYEAR | 20 | PC | 193,96 | 8001 | S226 | 101 | 23/09/2019 | 21/08/2019 | 33 |
| 54152691 | CORREIA 3VX 0425 GOODYEAR | 20 | PC | 193,96 | 8001 | S226 | 101 | 19/09/2019 | 21/08/2019 | 29 |
| 54152691 | CORREIA 3VX 0425 GOODYEAR | -20 | PC | -193,96 | 8001 | S226 | 102 | 19/09/2019 | 21/08/2019 | 29 |
| 54152691 | CORREIA 3VX 0425 GOODYEAR | 20 | PC | 193,96 | 8001 | S226 | 101 | 11/09/2019 | 21/08/2019 | 21 |
| 54152691 | CORREIA 3VX 0425 GOODYEAR | -20 | PC | -193,96 | 8001 | S226 | 102 | 11/09/2019 | 21/08/2019 | 21 |
| 54153386 | ELETRODO OK 46, ASME SFA5.1E6013 2,5MM | 15 | KG | 299,55 | 8001 | S226 | 101 | 29/08/2019 | 14/08/2019 | 15 |
| 54153386 | ELETRODO OK 46, ASME SFA5.1E6013 2,5MM | 20 | KG | 532,00 | 8001 | S226 | 101 | 03/05/2019 | 25/04/2019 | 8 |
| 54153386 | ELETRODO OK 46, ASME SFA5.1E6013 2,5MM | -1 | KG | -20,69 | 8001 | S226 | 201 | 01/03/2019 | 01/03/2019 | 0 |
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
- amitchandak
Super User
Anonymous , refer if these can help
https://bielite.com/blog/scale-down-outliers-power-bi/
https://datasavvy.me/2014/05/27/power-pivot-dynamically-identifying-outliers-with-dax/
- AnonymousNot applicable
I already saw these posts, and much another ones, but unfortully nothing is working to my needs. Some of my reasearchs bring me close solutions but I am stucked with the things that I say in the post, some lines is well calculated another ones is just wrong and I dont know the reason. But thanks for the your help. ❤️