Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to calculate percentile based on % of cumulative sum

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 NameOrders amount per provider descCumulative sumcumulative sum/total orders amount 
DB NETZ AG€ 3 000,00€ 3 000,0042,86%
BENE RAIL INTERNATIONAL NV€ 1 500,00€ 4 500,0064,29%
POST TELECOM PSF S.A.€ 1 000,00€ 5 500,0078,57%
STILOG IST SAS€ 800,00€ 6 300,0090,00%
KRAUSS-MAFFEI WEGMANN GMBH€ 500,00€ 6 800,0097,14%
CALPAM LUXEMBOURG SARL€ 200,00€ 7 000,00100,00%
Total orders amount€ 7 000,00€ 7 000,00100,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

 

1 ACCEPTED SOLUTION
2 REPLIES 2
Anonymous
Not applicable

Thank you so much amitchandak, I managed to do it with your first link!

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors