## Tableau Create Bins from a Measure Calculated Field- pt 1

This post about how to create bins from a measure in Tableau was originally written in the days of Tableau 7. Now things have evolved and it is far more straightforward, the updated article on using LOD calculations to create bins from a measure is here.

For this post I have to give a huge thanks to Richard Leeke who found the ‘Tableau only’ solution (as opposed to pre calculating the data) for this problem. As a quick overview for what I was trying to do using Tableau, I wanted to create a calculated field of which the result would be used to create bins. The calculated field is a measure, not a dimension, but the same rules apply.

The post is quite long and complex hence it’s broken up into multiple parts – the solution using data blending will be detailed in the next post, creating bins from a calculated field.

The test data has 3 columns: Month, ListingID and EnquiryCount – in other words it showed the enquiry count per listing per month. I wanted to calculate enquiries per listing over the entire time period and use the result of this calculation for the bins. The sum of these enquiries for each listing id defines which group they belong to – i.e. 1 – 10, 11 – 20, etc. In other words if ListingId 1 had an EnquiryCount of 10 in Month 1, 2 in Month 2 and 8 in Month 3, ListingId 1 received 20 enquiries in total so would be in the bin 11-20. Once I know which bin each listing belongs to I want to see for each group what % of total enquiries came in month 1, month 2 and month 3. For ListingId 1 50% of enquiries were received in Month 1, 10% in Month 2 and 40% in Month 3.

Depending on how the bins are to be used, due to Tableau partitioning constraints the bins recalculate (remember they are created from a calculated field) to return a result different to what it should be. This sentence will become clearer later in the post.

Other times you could run into a similar situation could be creating Revenue by Product, Orders by Salesperson, etc.

The first thing to do is create the bins. Create a calculated field to SUM(EnquiryCount) and call it EnqCountCalc. The result of this is going to be used for the bins in another calculated field called EnqCountBins, which is below:

This bin field can now be used without any problems for 1 dimensional reporting.

We want to know the number of unique ListingIDs that fit within each bin. We need to create another calculated field to do this doing a distinct count of ListingID, COUNTD(ListingID), which we’ll call ListingCount. (Note for COUNTD it’s best to use an extract – COUNTD isn’t available for all types of data connection.)

Drag the EnqCountBins pill to the Columns shelf and the ListingCount pill to the rows shelf and that gives the listing count. Both pills should be displayed like AGG(*CalcField*). You’ll notice the bins aren’t displayed correctly. To split the listing counts out into bins drag the ListingID pill on to the Level Of Detail and that completes the chart.

The complications begin when we want to use the result of the bin calculation AND perform further calculations which will affect the result returned – altering the bins dynamically.

As mentioned earlier in the post I’m actually trying to calculate in which months the enquiries were delivered to listings in each enquiry volume bucket…and this is where things rapidly get complicated.

To get the enquiry counts per bucket drag the EnquiryCount pill to Rows shelf in the place of ListingCount. This should be a SUM(EnquiryCount) calculation. Next we want to split the enquiry counts by month. The obvious thing to do is to drag the Month pill into the Colour shelf. Do that and you see things start to go wrong. This is because the bins have now recalculated since they are a calculated field.

I have to attribute the solution wholly to Richard Leeke as he worked it out . Data Blending is the answer – which in effect is like a self join in SQL. As things begin to get difficult now I’ve written it up in a separate post.

This problem can also be solved by using a fixed calculation level method. By creating ‘EnqCountCalc’ using a fixed level calculation you can have Tableau create the bins. Here is the Calculation example:

{Fixed ListingID: sum([EnquiryCount])}

This formula will sum all the inquires per ListingID across all the months, and keeping it fixed.

Next you can use Tableau’s menu to create the bins on this calculation.

Thanks, the introduction of LOD calculations made this type of thing straightforward. It used to be VERY complicated.

I should write an updated article about this really…