4 Replies Latest reply on Oct 20, 2016 8:59 PM by Mikey Michaels

# Create custom bins

Hello All,

I have been tasked with a project where I do not even know where to start!

I need help creating the following:

• Custom revenue bins 0-25K, 25K-50K,  50K-75K, 75K - 250K, >500K
• In these buckets, I need to see where each Global DUNS customer's revenue falls
• I need a formula to look at revenue from 2014 (2014 - License plus Hardware), 2015 (2015 - License plus Hardware), and 2016 (2016 - License plus Hardware) and determine over the last 3 years, where did the Global DUNS customer's largest revenue fall within the custom revenue bucket.
• For example, Global DUNS 0032 had the following revenue:

•  Global DUNS number Global DUNS name 2014 - License plus Hardware 2015 - License plus Hardware 2016 - License plus Hardware 0032 NOLOGY 60,879.89 817.90 0.00

Using the methodology explained above, this customer would fall into the 50K-75K bin. I would like the bins to count the number of Global Duns that falls into each.

Whew, I hope I explain my issue well enough!

As always, thank you so much for taking a look!

Regards,

Mikey

• ###### 1. Re: Create custom bins

Apologies, as this may be a partial response to your problem.   Have you looked at creating custom bin sizes? Creating Variable Sized Bins | Tableau Software .   This will help you get the counts per bin.   However, to determine the sum (dollars) across the years and determining the count may need some thought.   If a DUNS fell in one bucket for one year, but another bucket for another year, how do you want to account for that?

• ###### 2. Re: Create custom bins

Hi Srinidhi,

And thank you for the article -> this will definitely help me create the buckets.

So I need to place each Global DUNS into only ONE bucket. I would need some sort of formula that would look at the 2014, 2015 and 2016 revenue individually, and place that Global DUNS into the bucket where the revenue was the highest over the 3 years. I would like to chart the count or number of customers that fall into each bucket

For instance: Customer ABC

2014     2015     2016

10K       150K        5K

Since the revenue for customer ABC for 2015 was the highest @ 150K, I would put this customer into the 75K - 250K bucket.

Thanks for taking a look!

• ###### 3. Re: Create custom bins

Here is a potential solution.  Please note that this is based on how your data is structured.  Typically I would see this type of data pivoted by year and flattened out, but the solution proposed below will work with the way you currently have the data structured:

1.  Create a calculation as follows -  call it  'Max Value':

2. Next create another calc - call it "Value Bins"

IF (([Max Value] <= 25000) and [Max Value] > 0) then '0-25k'
ELSEIF (([Max Value] <= 50000) and [Max Value] > 25000) then '25-50k'
ELSEIF (([Max Value] <= 75000) and [Max Value] > 50000) then '50-75k'
ELSEIF (([Max Value] <= 250000) and [Max Value] > 75000) then '75-250k'
ELSEIF   (([Max Value] <= 500000) and [Max Value] > 250000) then '250-500k'
ELSE '>500k'
END

3.  Now, create a new viz and drop "Value Bins" on Columns, and the "Number of Records" pill onto Rows.  You should get a bar chart viz that looks like this:

Let me know if that is what you wanted.

I have the workbook attached, but it is saved in a later version (10.0) than your original, so not sure if you can open it.  Hence the instructions.

• ###### 4. Re: Create custom bins

This is excellent...thank you, Srinidhi!