The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi everyone!
I am having some throble trying to remove some outliers of my calculation. I have lines that is well calculated, using these formulas:
Q1 = PERCENTILE.INC(Sheet1[Lead], .25)
Q3 = PERCENTILE.INC(Sheet1[Lead], .75)
IQR = [Q3]-[Q1]
Max = [Q3]+[IQR]*1.5
But some of my lines is just ignored by the formulas even being possible to calculate. I tried to calculate some of blanklines in excel and that was possible to calculate.
Look this print:
Line 2, 4, 6 are exemples of not calculating lines. Everything is zero but my database of couse exist data to calculate Q3 and Q1
here is my datasample
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 | 15 | KG | 330,90 | 8001 | S226 | 101 | 23/07/2020 | 22/01/2020 | 183 |
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 | 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 | 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 | -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 | 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 |
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 | 18 | KG | 643,60 | 8001 | S226 | 101 | 08/07/2020 | 29/06/2020 | 9 |
54153386 | ELETRODO OK 46, ASME SFA5.1E6013 2,5MM | 5 | KG | 100,10 | 8001 | S226 | 101 | 13/12/2019 | 05/12/2019 | 8 |
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 | 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 | -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 | -2 | KG | -79,46 | 8001 | S226 | 102 | 28/10/2020 | 28/10/2020 | 0 |
54153386 | ELETRODO OK 46, ASME SFA5.1E6013 2,5MM | 2 | KG | 79,46 | 8001 | S226 | 101 | 28/10/2020 | 28/10/2020 | 0 |
Thanks in advance!
Hi @Anonymous,
Can you please share a pbix file with some dummy data to test?
How to Get Your Question Answered Quickly
I test will your sample data but the formulas not reproduce your issue, they all get similar results.
Regards,
Xiaoxin Sheng
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
125 | |
106 | |
84 | |
64 | |
59 |
User | Count |
---|---|
257 | |
121 | |
116 | |
100 | |
79 |