5 Replies Latest reply on Aug 30, 2016 4:26 AM by Shriram Saxena

# Dynamic Categorization of sales and finding number of customers

Hi ,

I have a case to categorize the total sales in 10 equal brackets (10 % of TOTAL sales) and identify the number of customers whose total sales falls under each category.

For example, if total sales is 10000, and in the range of 5000-6000, there 56 customers whose individual total sales falls in this range and they constitute 16.16% of the total customers.

I have attached the details in an excel.

I do have some filters, the category and the result automatically readjust based on the filter selection.

Can any one help.

• ###### 1. Re: Dynamic Categorization of sales and finding number of customers

Hi Mathew,

Is this what you are looking for .

Best

Shriram Saxena

• ###### 2. Re: Dynamic Categorization of sales and finding number of customers

Hi Saxena,

Thanks very much for your solution.

But I have one problem in using BIN as I need to divide total sales into 10 equal parts..

Assuming the Total is 2297201, it should be as below and need to find number of customers falling whose total sales falls into each category.

TOTAL - 90 % OF TOTAL -sum([Sales])>229720.1* 9 and sum([Sales]) <=2297201

90 % OF TOTAL - 80 % TOTAL  - sum([Sales])>229720.1* 8 and sum([Sales])< =229720.1* 9

80 % OF TOTAL - 70 % TOTAL  - sum([Sales])>229720.1* 7 and sum([Sales])<=229720.1* 8

70 % OF TOTAL - 60 % TOTAL  - sum([Sales])>229720.1* 6 and sum([Sales])<=229720.1* 7

60 % OF TOTAL - 50 % TOTAL - - sum([Sales])>229720.1* 5 and sum([Sales])<=229720.1* 6

50 % OF TOTAL - 40 % TOTAL - - sum([Sales])>229720.1* 4 and sum([Sales])<=229720.1* 5

40 % OF TOTAL - 30 % TOTAL  - sum([Sales])>229720.1* 3 and sum([Sales])<=229720.1* 4

30 % OF TOTAL - 20 % TOTAL  - sum([Sales])>229720.1* 2 and sum([Sales])<=229720.1* 3

20 % OF TOTAL - 10 % TOTAL  - sum([Sales])>229720.1* 1 and sum([Sales])<=229720.1* 2

0 - 10 % OF TOTAL  - sum([Sales])>0 and sum([Sales])>229720.1

Thanks...

• ###### 3. Re: Dynamic Categorization of sales and finding number of customers

Hi Mathew,

Try creating Variable SIze bins

Best

Shriram Saxena

1 of 1 people found this helpful
• ###### 4. Re: Dynamic Categorization of sales and finding number of customers

Hi Saxena,

The problem with me is that the sales is dynamic and so the percentage also not just a variable alone.

I cannot categorize based on any fixed values.

Thanks.

• ###### 5. Re: Dynamic Categorization of sales and finding number of customers

Hi Mathew,

I think this will solve your problem.

Pls find the Workbook attached...(see Sheet 5)

Best
Shriram Saxena