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

# Binned sum of revenue based on rank percentile

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

Hi Thrennial,

Could you please share a workbook?

Yours,

Yuri

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

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 -