2 Replies Latest reply on Feb 10, 2019 4:18 AM by Jim Dehner

    Binned sum of revenue based on rank percentile

    Thrennial

      Hi there,

       

      I have a set of revenue data per sales person in a format such as the below:

      personsales_revenue
      James$100
      John$50
      Newt$10
      Pamela$1000
      Matt$1
      Johnson$1
      Matilda$1
      Aaron$1
      Patrick$1
      Sonic$15

       

      The desired outcome is that I have their rank percentiles in bins of 20% and the sales revenue contribution in raw numbers and percentage of this bin of sales people.

      0%=<x<20% Percentile20%=<x<40% Percentile40%=<x<60% Percentile60%=<x<80% Percentile80%=<x<100% Percentile
      $1100$65$11$2$2
      93.2% (1100/1180)5.5% (65/1180)0.9% (11/1180)0.0% (2/1180)0.0% (2/1180)

       

      Ideally this is represented via a histogram or line chart of sorts.

       

      I've been able to output the rank_percentiles and individual sales contribution but I'm stuck on how to proceed further.

       

      Would love any tips or guidance on how to get this done.

       

      Thanks!

        • 1. Re: Binned sum of revenue based on rank percentile
          Yuriy Fal

          Hi Thrennial,

           

          Could you please share a workbook?

          It could help others to help you.

           

          Yours,

          Yuri

          • 2. Re: Binned sum of revenue based on rank percentile
            Jim Dehner

            Good morning

            Agree with Yuri - - but I have done this sort of analysis before - in my application I looked at superstore sales at the state level and year - it takes some work so I will just so your

            directionally where to go

            the first thing you is determine the percentile boundary for each group with a formula like this - you will need 5 - 1 for each percentile group

                      { EXCLUDE [State]:    sum({ INCLUDE   [Category]: (PERCENTILE( { INCLUDE   [State],year([Order Date]),[Category]:sum([Sales])}  ,.8) )})}

            then the group definitions become

                 if ([fixed sales at state year cat])<sum(([percentile .2])) then 'Group 1'

                 elseif ([fixed sales at state year cat])<sum([percentile .4]) then 'Group 2'

                 elseif ([fixed sales at state year cat])<sum([percentile .6] )then 'Group 3'

                 elseif ([fixed sales at state year cat])<sum([percentile .8] )then 'Group 4'

                 elseif ([fixed sales at state year cat])<=sum([percentile 1.0]) then 'Group 5'

                 else 'other' end

             

            you should be able to take that and get going on your analysis -

            if you run into trouble post your book and one of us will help you out

             

            Jim

             

            If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.