I was helping out a friend earlier this week and, in the process of helping him build the visualization he wanted, I was able to create a workaround for a problem I have run into before that I thought would be worth sharing. The problem this workaround solves is grouping bins on a histogram.
Tableau has the awesome capability to build bins and instantly create a histogram. However, it comes with a couple limitations. First, you cannot group the bins. As far as I understand it, and I may be completely off on this, the issue has to do with how Tableau creates the bins as a new Dimension; the bin itself almost acts as a the group rather than allowing a group functionality. (Side note: feel free to add comments and improve on this explanation of why grouping doesn't work. I am not a technical expert on the inner workings of Tableau). You can create sets, but the lack of being able to highlight and group has been a frustration where I work since it is so easy to do in other situations. A second limitation is that you cannot use the bins in calculations. Again, you can use sets, but if this can cause issues if you want to show one level of detail on those values in the set and one level on those values not in the set.
This is not the best of views for many reasons, but one of the main ones is the long tail to the right needed to capture the different sales amounts. Normally, in a view with marks rather than bins, it would be possible to highlight all the values to the right and group them to create a view that showed the values up to a certain value, then have a bin that includes everything greater than or equal to that value. However, because you can't group bins, a workaround is necessary. I'll do my best to document each step to show how I solved this issue. I'll also attach the workbook so people can play around with the viz and make improvements. I'm sure people will come up with much better and more elegant ways to do this. If you do, please let me know so I can learn from you as well!
The first step in the process is to create a set for those values you want to group. In the Sample - Superstore data set, I chose Sales as the field I wanted to use. The trick is, as a continuous measure, you cannot create a set. Sets are only created off of Dimensions. So, I duplicated the Sales measure and converted the duplicate to a Dimension, renaming it Sales - Dimension in the process.
Before creating the set off of this new field, I also created a parameter called "Group everything above..." In my experience, I have found it helpful to create a viz that offers flexibility from the beginning rather than trying to add it in later. With the parameter, the user will be able to choose what level they want to group at, making a much more flexible view.
Back to creating the set, I used the new Sales - Dimension field to create my set. Instead of hard-coding values though, I created a condition using the following forumla: [Sales - Dimension] >= [Group everything above...]. This will dynamically update the members of the set based off of the parameter value the user chooses. In the attached workbook, I named this set "Fields to Group". Not the most exciting name, but descriptive nonetheless.
After creating the set, I needed to create the bins. However, looking forward, I also will need to be able to use these bins in a calculation, so I had to not use Tableau's built-in binning capability. To manually create the bins, I used the following formula: INT([Sales - Dimension]/[Bin Size])*[Bin Size]. In this case, Bin Size is another parameter, again, created to provide flexibility with the final view. The calculation works by using the INT function to truncate the decimal point off of the returned value (int(4.1) = 4; int(4.6) = 4). Then, multiplying by the Bin Size again will create the relative bins by returning values that will all match due to the truncation. I called this field Bins.
For example, a data set of 4, 5, and 15 sized at bin size 10 will calculate as follows: int(4/10) * 10 = int(.4) * 10 = 0*10 = 0; int(5/10)*10 = int(.5)*10 = 0*10 = 0; int(15/10) = int(1.5) * 10 = 1*10 = 10. These will return a value of 0, 0, and 10, allowing you to use the discrete values returned by the calculation as your own bins. A quick test by dragging Bins to the Columns shelf, changing it to discrete, and placing CNT(Sales) on the Rows shelf will recreate a the view that Tableau would create if you created a histogram using Tableau's built in capabilities.
Once you have your bins created and the set made, the last step is to create a calculated field that will act as your bins, showing both the individual bins below your group, as well as the group itself. I built this in a field called Custom Histogram that looks like so:
if [Fields to Group] = FALSE then STR([Bins]) else STR([Group everything above...])+'+' end
The If first looks to the set you created to see if it is False. This will return every bin that you want to see broken out, not grouped. For each of these bins, all it does is return the value of that bin, cast as a string. The else statement is a dynamic label that takes whatever cutoff the user put in using the parameter Group everything above... and concatenates it with a +. This allows for a slightly more descriptive label of 650+, 750+, etc. instead of just the minimum bin in your group. It also is the reason you have to cast both Bins and Group everything above... as strings to make it possible for you to concatenate it with a string value.
Once you have that calculation built, drag it onto the Columns Shelf, add CNT(Sales) to your Rows Shelf, show the parameter controls for Bin Size and Group everything above...and you're good to go! Here is what the final view looks like:
I'm sure I've just scratched the surface of possibilities like this. Like I said before, feel free to use this technique and improve on it, just let me know what you find out so we can all keep getting better at exploring our data.
Hope this helps!
EDIT Nov 17, 2017: I expanded on this technique to include how to handle negative values as well: https://www.interworks.com/blog/byoung/2017/11/17/how-create-bins-histogram-tableau
Custom Histogram.twbx 1.2 MB