-
1. Re: Binned metric
Jim DehnerMar 13, 2017 7:49 AM (in response to Narbukra Lakina)
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 Mar 13, 2017 3:23 PM (in response to Jim Dehner)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 DehnerMar 14, 2017 7:52 AM (in response to Narbukra Lakina)
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
- determine the total sales >>{ Fixed (YEAR([Order Date])=2016): SUM([Sales])}
- I hard coded in the year 2016 you could use a parameter
- create a running total % of total sales >>RUNNING_SUM(sum([Sales])/sum([fixed sales]))
- 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
-
4 group excel check.xlsx 69.9 KB
-
fixed average.twbx 1.3 MB
- determine the total sales >>{ Fixed (YEAR([Order Date])=2016): SUM([Sales])}
-
4. Re: Binned metric
Narbukra Lakina Mar 17, 2017 10:24 PM (in response to Jim Dehner)Thank you that's helpful!