Splitting data into quantiles is often a useful way to examine the distribution of data. This method shows a way to let the user freely change the width of these quantile intervals, with no restriction on the number of intervals they wish to split to. I show how to do this using a combination of a parameter and a table calculation. This is a development of a previous post I have written on splitting data into quintile intervals, which saves the need for writing a custom IF statement for every size of interval made available to the user. Not only would this be tedious, but it would unnecessarily restrict the user to the widths of intervals the author had the patience to code. This way the user can choose the familiar quartiles, quintiles, deciles and percentiles, or go beyond these if needed.

As an example I have chosen to create a bar chart using anonymised US income data (available here), with non-earners removed. Let’s start by creating the parameter. This will be the number the user can choose to decide how many quantile intervals they wish to split to. For example, if ‘4’ is selected, the data will be split into quartiles. First right-click in the data pane, and choose Create Parameter… . After naming your parameter something memorable (I chose ‘Select Quantile Interval’), select Integer from the Data Type drop-down. Then from Allowable Values select Range, and choose a minimum and maximum number of intervals that you wish the user to be able to select from (1 and 100 was enough for me). Click Ok, and you should see your parameter appear at the bottom of your data pane. Right click on it and select Show Parameter.

Now we need to put this parameter to work in a table calculation. First make sure you are clear which of your measure fields is going to be used to determine which quantile interval each record falls into. For me, this was the [Yearly Earnings] field, which gave the amount earned by each person in the data. At the top of your screen, go to Analysis > Create Calculated Field, and enter the following piece of code:

Even though this might look like a calculated field, the RANK_PERCENTILE function we are calling is in fact a table calculation. Frustratingly, if Tableau allowed the CEILING function to work with table calculations then this could all be written more elegantly as CEILING(RANK_PERCENTILE(SUM([Yearly Earnings]))*[Select Quantile Interval]), however as things stand we are required to write our own ceiling function, hence five lines of code instead of one.

We are now ready to build the view. Put the new Quantile Interval pill on columns, and your chosen measure on rows (for me, this was Yearly Earnings). At this point, no matter what number is selected in your parameter, there will only be one quantile interval on display in your view. This is because we need to tell Tableau to treat each row of data separately when it compiles the ranking that goes into the RANK_PERCENTILE function. In my data, each person had a unique ID number associated them, so to build my view all I need to do is drag this unique identifier field onto the Detail shelf. Finally, right click on the Quantile Interval pill on columns, and select Edit Table Calculation, and make sure your table calculation is computed using Specific Dimensions, ticking the box with the name of your unique identifier field. In my final bar chart, I wanted each quantile’s sum of earnings to be represented as a percentage of the total earnings, which I did by right clicking the Yearly Earnings pill on columns, and selecting Quick Table Calculation > Percent of Total.