Pank by percentiles rather than range

[domtrump]

Currently we put our customers into "buckets" based on total (annual) spend using banding.

Customer base:

I've been asked to rank using percentiles instead to look for patterns based on promotions. i.e. of the patrons who respond to a particular promotion, are they in the top 10% of the customer base based on "annual spend" .

Try to explain more: 

lets say that of the 3510 customers in the customer base, 100 patrons respond to a promotion. how many of those 100 patrons are in the top 10% of the customer base based on annual spend. how many are in the bottom 20% of the customer base based on annual spend?

Does this question make sense (if not, what additional information would you need?)? If you could point me in the right direction..

Thanks.

[Anonymous]

Hi @domtrump ,

To find the top 10% of the customer base based on annual spend, you can use the following formula:

Top 10% = PERCENTILEX.INC(CustomerBase, 0.9, [Annual Spend]) 

To find the bottom 20% of the customer base based on annual spend, you can use the following formula: 

Bottom 20% = PERCENTILEX.INC(CustomerBase, 0.2, [Annual Spend])

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[domtrump]

RANK, lol. could not find an option to correct the title.. ADM if you could fix