Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |