8 Replies Latest reply on Sep 8, 2016 6:59 AM by P R

# Categorize Customers by % of Sales

Hi all, I have the sales table as shown below. I need to categorize my customers into three categories, A, B and C. The customers making up to 80% of total sales are classified 'A'. The next 15% are classified 'B' and the remaining 5% are classified 'C'.

In the below example ,

• Total sales = 40.Therefore  Bucket A =32 (80%) , Bucket B = 6 (15%) , Bucket C = 2 (5%)
• C5=19, c1=13,c3=6,c2=2
• So Customers C5 and C1 make up 80% and fall in Bucket A. C3 fall in Bucket B and C2 fall in Bucket C

How do I go about achieving this? I need a calculated column because I need to apply filters and have these change dynamically as well. Any help will be appreciated. I tried

SUM([Sales]))<=SUM({SUM([Sales])})*.8

THEN 'A'

ELSE 'B'

END

It works but it depends on Customer Sort order. I need it to be independent of sort order.

CustomerSales
C110
C36
C13
C22
C519
• ###### 1. Re: Categorize Customers by % of Sales

Praveen:

You have to do a running sum and calculate % of total. Once you have that calc field, you can then create another calc to define A, B, and C.

Calc1:

RUNNING_SUM(SUM([Sales])) / TOTAL(SUM([Sales]))

Calc 2:

if [Run Sum Total] < .8 then "A"

elseif [Run Sum Total] < .95 then "B"

else "C"

END

Hope this helps.

..kk

2 of 2 people found this helpful
• ###### 2. Re: Categorize Customers by % of Sales

One other way to do using a single calculated field is using percentile option.

if RANK_PERCENTILE(SUM([Sales])) > .2

then 'A'

ELSEIF (RANK_PERCENTILE(SUM([Sales])) < .2 and

RANK_PERCENTILE(SUM([Sales])) >= .05 )

then 'B'

ELSE 'C'

END

Note :- You would need to format your sales field in the rows to Number(Custom) with Decimal places 2

Good Luck !!!

1 of 1 people found this helpful
• ###### 3. Re: Categorize Customers by % of Sales

Hi ,

I tried what you said but I am facing the same issue. If I sort on Customer Name, the value changes. It should not be dependent on load order or any other sort category and just be dependent on absolute sales. Can you please see take a look at the workbook attached  ?

• ###### 4. Re: Categorize Customers by % of Sales

Thank you for the reply Anoop. I am guessing Rank function is way to go. I tried using your function but still not getting the desired result.  Please see attached. Any suggestion?

• ###### 5. Re: Categorize Customers by % of Sales

Check this out. I have published in Public as I don't have a desktop in my personal computer.

• ###### 6. Re: Categorize Customers by % of Sales

Anoop, thanks for the solution, I feel I should explain in slightly more detail. If you see the below image, C1 and C5 make up 80% and should be in category A , C3 in B and C2 in C.Basically rank the customers by sales, calculate running total percentage and club those making up to 80% in Bucket A and so on.. Hope this helps

• ###### 7. Re: Categorize Customers by % of Sales

You can try this

Let me know If this help

Mahfooj

2 of 2 people found this helpful
• ###### 8. Re: Categorize Customers by % of Sales

That's it. LOD to the rescue. Thank you so much