Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I am facing a quite complex request I'm afraid I am not able to solve without your help 😕
Basically, I have data with providers and orders amount per providers (2 first columns in my table below).
I need to calculate the percentile (20 and 70) based on the percentage of cumulative sum of order amounts compared to total orders amount.
That is to say for my example :
"20% of my providers (doing the biggest orders), represent 51.43% of my orders, 3600€ in amount."
"70% of my providers (doing the biggest orders), represent 96.43% of my orders, 6920€ in amount."
Provider Name | Orders amount per provider desc | Cumulative sum | cumulative sum/total orders amount |
DB NETZ AG | € 3 000,00 | € 3 000,00 | 42,86% |
BENE RAIL INTERNATIONAL NV | € 1 500,00 | € 4 500,00 | 64,29% |
POST TELECOM PSF S.A. | € 1 000,00 | € 5 500,00 | 78,57% |
STILOG IST SAS | € 800,00 | € 6 300,00 | 90,00% |
KRAUSS-MAFFEI WEGMANN GMBH | € 500,00 | € 6 800,00 | 97,14% |
CALPAM LUXEMBOURG SARL | € 200,00 | € 7 000,00 | 100,00% |
Total orders amount | € 7 000,00 | € 7 000,00 | 100,00% |
percentile 20 % : PERCENTILE.EXC(D2:D7;0,2) | percentile 20 amount : PERCENTILE.EXC(C2:C7;0,2) | ||
51,43% | 3 600 € | ||
percentile 70 % | percentile 70 amount | ||
96,43% | 6 920 € |
Thanks a lot for your help, much appreciated as always on this forum! AnneSo
Solved! Go to Solution.
@Anonymous , refer these
Top 80/20 , percent /percentile
https://blog.enterprisedna.co/implementing-80-20-logic-in-your-power-bi-analysis/
https://forum.enterprisedna.co/t/testing-the-pareto-principle-80-20-rule-in-power-bi-w-dax/459
https://finance-bi.com/power-bi-pareto-analysis/
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculate-the-sum-of-the-top-80/td-p/763156
@Anonymous , refer these
Top 80/20 , percent /percentile
https://blog.enterprisedna.co/implementing-80-20-logic-in-your-power-bi-analysis/
https://forum.enterprisedna.co/t/testing-the-pareto-principle-80-20-rule-in-power-bi-w-dax/459
https://finance-bi.com/power-bi-pareto-analysis/
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculate-the-sum-of-the-top-80/td-p/763156
Thank you so much amitchandak, I managed to do it with your first link!
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
54 | |
35 | |
19 | |
19 | |
15 |