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

    Categorize Customers by % of Sales

    P R

      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