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

# Binned metric

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

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

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

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 Name labels fore25% of sales Sales Raymond Buch 75-100% \$14,203 Tom Ashbrook 75-100% \$13,723 Hunter Lopez 75-100% \$10,523 Seth Vernon 75-100% \$8,460 Row Labels Sum of Sales Count of Customer Name Grant Thornton 75-100% \$8,167 0-25% \$184,415 472 Helen Wasserman 75-100% \$8,166 25-50% \$183,345 125 Todd Sumrall 75-100% \$6,702 50-75% \$183,025 67 Rick Wilson 75-100% \$6,193 75-100% \$183,161 29 Pete Kriz 75-100% \$5,979 Grand Total \$733,947 693 Karen Ferguson 75-100% \$5,825 Andy Reiter 75-100% \$5,821 Nick Crebassa 75-100% \$5,773 Caroline Jumper 75-100% \$5,723 Brian Moss 75-100% \$5,683 Alan Dominguez 75-100% \$5,434 Jane Waco 75-100% \$5,385 Yana Sorensen 75-100% \$5,340 Patrick O'Brill 75-100% \$5,308 John Lee 75-100% \$5,302 Shahid Collister 75-100% \$5,259 Joseph Airdo 75-100% \$5,054 Tom Prescott 75-100% \$4,827 Cathy Prescott 75-100% \$4,743 Brenda Bowman 75-100% \$4,586 Justin Deggeller 75-100% \$4,508 Jim Kriz 75-100% \$4,151 Dan Reichenbach 75-100% \$4,132 Bill Eplett 75-100% \$4,102

Let me know if this helped

Jim