4 Replies Latest reply on Mar 17, 2017 10:24 PM by Narbukra Lakina

    Binned metric

    Narbukra Lakina

      Hello,

       

      I want to create a binned metric that indicates the range of the spend of a customer.

      This should be calculated at the customer level for all the data, independent from the window filters.

       

      For example:

       

      10K  0-25%

      30K  25-50%

      50K  50-75%

      70K  50-75%

      90K  75-100%

       

      I used this formula but it is not returning correct result:

       

      IF RANK_PERCENTILE(avg({fixed [Customer Id]: SUM( [Spend]  )}), 'asc')<= 0.25 THEN '0-25'

      ELSEIF RANK_PERCENTILE(avg({fixed [Customer Id]: SUM( [Spend]  )}), 'asc') <= 0.50 THEN '25-50'

      ELSEIF RANK_PERCENTILE(avg({fixed [Customer Id]: SUM( [Spend]  )}), 'asc') <= 0.75 THEN '50-75'

      ELSEIF RANK_PERCENTILE(avg({fixed [Customer Id]: SUM( [Spend]  )}), 'asc') <= 1 THEN '75-1'

      END

       

      Any help?

        • 1. Re: Binned metric
          Jim Dehner

          Hi

          I don't know what results you expected but the formula you wrote will divide all the Customer IDs in 4 equal groups by count (I.E. 25 % of the customer ID's in each group regardless of how much they spend. If you wanted them grouped by the SPEND range in your problem statement then the formula would look like

           

          IF sum([spend])<10000 then '0-25'

          Elseif sum([spend])<30000 then  '25-50'

          elseif sum([spend])<75000 then '50-75-'

          else '100' end

           

          So - don't know if this helped but it all depends on your expectations

          Jim

          • 2. Re: Binned metric
            Narbukra Lakina

            Hi Jim, thanks for your answer.
            I actually want to have grouping by the Spend range providing that the Spend may change overtime, so I cannot define "Static" statements base don current spend

             

            • Range '0-25' should include the first 25% bucket of the Spend values (<= 25th percentile)
            • Range '25-50' should include the next 25% of the Spend values (25th percentile and 50th percentile)
            • etc.

            Using the example data, the result should be

            {10K}: 0-25%

            {30K}: 25-50%

            {50K, 70K}: 50-75%

            {90K}: 75-100%

             

            How can I update my formula?

            • 3. Re: Binned metric
              Jim Dehner

              Good Morning

               

              This was more difficult than it appeared - every time I tried using the table calculations for percentile it determined the groupings by record count and not total sales - I did not think it was suppose to work that way -

              So I did it the by creating a series of formulas to do the calculation the way you would in excel

              I used superstore data

              1. determine the total sales >>{ Fixed (YEAR([Order Date])=2016):  SUM([Sales])}
                1. I hard coded in the year 2016 you could use a parameter
              2. create a running total % of total sales >>RUNNING_SUM(sum([Sales])/sum([fixed sales]))
              3. Break the running total into 4 parts >>if [Assign % by total sales] < .25 then '75-100%'

                            Elseif [Assign % by total sales] < .5 then '50-75%'

                            Elseif [Assign % by total sales]< .75 then '25-50%'

                            else '0-25%' end

              To get this to work the sales had to be sorted descending - not what I wanted but the only way I could get it to work

               

              here is screen shot

              Here is a portion of the attached excel I used to check the totals - note the sales $ are approx equal in each group

               

                    

              Customer Namelabels fore25% of salesSales
              Raymond Buch75-100%$14,203
              Tom Ashbrook75-100%$13,723
              Hunter Lopez75-100%$10,523
              Seth Vernon75-100%$8,460Row LabelsSum of SalesCount of Customer Name
              Grant Thornton75-100%$8,1670-25%$184,415472
              Helen Wasserman75-100%$8,16625-50%$183,345125
              Todd Sumrall75-100%$6,70250-75%$183,02567
              Rick Wilson75-100%$6,19375-100%$183,16129
              Pete Kriz75-100%$5,979Grand Total$733,947693
              Karen Ferguson75-100%$5,825
              Andy Reiter75-100%$5,821
              Nick Crebassa75-100%$5,773
              Caroline Jumper75-100%$5,723
              Brian Moss75-100%$5,683
              Alan Dominguez75-100%$5,434
              Jane Waco75-100%$5,385
              Yana Sorensen75-100%$5,340
              Patrick O'Brill75-100%$5,308
              John Lee75-100%$5,302
              Shahid Collister75-100%$5,259
              Joseph Airdo75-100%$5,054
              Tom Prescott75-100%$4,827
              Cathy Prescott75-100%$4,743
              Brenda Bowman75-100%$4,586
              Justin Deggeller75-100%$4,508
              Jim Kriz75-100%$4,151
              Dan Reichenbach75-100%$4,132
              Bill Eplett75-100%$4,102

               

               

              Let me know if this helped

              Jim

              • 4. Re: Binned metric
                Narbukra Lakina

                Thank you that's helpful!