Define Top 20% out of remaining 70% of sales amount

[Cjulien10]

Hello everyone,

I am working for a project where I need to define some “Benchmark” users based on the sales amount.

The way how to do it is:
- Remove the top 10% count of users
- Remove the bottom 20% count of users.
- Out of the 70% remaining, I take the top 20% to define them as “Benchmark”.

I am able to define the top 10%, bottom 20% in the same column but not the top 20% out of the 70% remaining.
This is where I am struggling, I do not know how to define the range based on those 70% to use the percentile calculation used for the top 10%, bottom 20%

Users|Sales Amount | Combine Top10% and bottom 20% | Expectation
___H____10000___________________out_________________out
___F_____9000___________________keep____________Benchmark
___D_____8000___________________keep_______________out
___I_____7000___________________keep_______________out
___A_____6000___________________keep_______________out
___J_____5000___________________keep_______________out
___C_____4000___________________keep_______________out
___G_____3000___________________keep_______________out
___B_____2000___________________out________________out
___E_____1000___________________out________________

How to define the 20% based on the "7 lines" left ?`

Please find as well an Excel file attached which shows the table above.

Ps: unfortunately, I can't change the rules...

Thank you a lot for the future help,
Regards,

[Olly]

Do you need the full table output, or are you just interested in returning the list of "Benchmark" users?

[Cjulien10]

Hi,
If you can make it to return the "Sales amount" value, even better !

Thank you

[Olly]

This returns the benchmarks User(s) & Sales Amount(s), using Power Query:

Please Login or Register  to view this content.

Capture.PNG

[Cjulien10]

Perfect, thank you a lot ! that works great

[Olly]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.