Creating the Top 5 with a %Total in a Specific Month within Tableau

A question came up in one of our internal training sessions on Tableau. Mapping the question into the usual Superstore sample in Tableau, we wanted to create a table of the top 5 customers by total sales within a specified month and showing the percentage of the total sales they accounted for in that month. As this sample set has a parameter of the number of top customers I decided to use that rather than the fixed number of 5 for this post.

I am far from an expert at Tableau but I learnt a fair amount trying to work out different and simple ways to do this. I am sure there are probably a million ways to do this and expect probably overlooked the simplest! However I learnt a fair few fun tricks trying to build it, with each iteration getting simpler and simpler.

image

Starting with a graph with Customer Name on the Rows and Sum(Sales) in column filtered to the specified month (December 2014 in this case). My first attempt was to create a filter to the specified month (December 2014) and then to add a filter to the Customer Name to top five by Sum of Sales:

image

This resulted in a set of two elements which confused me at first. The filter on customer name is applied on raw data before the filter on Order Date is applied. As we want the top customers within a specific month this didn’t help.

Method 1 – Calculated Field providing sales within specified month

First successful method was by creating a calculated field of ‘FilteredSales’ which would only have the Sales values in the filter.

image

Applying this to the Customer Name filter instead of Sales worked great to get the Top 5 within December. Next step was to convert to a parameter to allow date selection rather than fixing within the formula.

image

Tableau allows you set range from an existing field, In the real case I was doing it was linked to a live dataset which was updated once a month, so we didn’t fix the maximum. Then updated the calculated field to be:

image

This gave the list correctly and showed their monthly totals:

image

The next question was how to get a total of the whole month. Creating a set gives you a Boolean (true/false) as to whether a specific name is within the top 5. Right click on Customer Name then you can create a set on Top FilteredSales. Using this, I create a second calculated FilteredCustomerName:

image

Drag this in place of Customer Name in the Rows shelf and remove the Customer Name filter, then we are getting pretty close to what we need. Next step is to create a percentage of the total (available the via the Quick Table Calculation menu), hide the ‘Other’ row and finally sort on value:

image

Method 2 – Context Filters and Fixed Level of Detail

I had come across context filters in relation to performance, but they gave us an alternative way to doing to creating this view of the data. Back to the starting point with the list of Customer Names against sum of Sales filtered to a specific month, if you right click on the MY(Order Date) pill in the Filters shelf, you get the option to convert to a Context Filter:

image

Clicking this will turn the pill a grey colour. This filter is then applied much earlier in the Tableau computation process, importantly in this case, it is applied before Dimension filters and all level of detail calculations. Bora Beran has a nice post on Level of Detail calculations and he shows the order filters are applied and when Level of Detail calculations are performed. Having added the pill to the context, apply a Top N by Sum of Sales filter to the Customer name and we get the right set of names:

image

Last challenge is to get percentage of total. As the data is filtered to the top 5 customers a table calculation gives the percentage of just these ones. As Fixed Level of Detail calculations are performed after the context filters but before the dimension filters, they are at the exact place I need. Last step was to edit SUM(Sales) pill to be divided by the Total SUM(Sales) across all names:

SUM([Sales]) / SUM({SUM([Sales])})

image

Method 3 – Rank Filter

Finally the simplest method I came up with, using a table level filter. Part of me likes the context filter and fixed level of detail method, but the flexibility of this method is huge. The rank does not depend on whatever other filter you have so you can then drill into region and so forth.

So back to the starting point once again, and then right click on the Sum(Sales) and select ‘Percent of Total’ from the Quick Table Calculation menu. Add this to the label as well and we have an unfiltered version of what we need:

image

Now we need to create the filter. Double click in the white space at the bottom of the Marks shelf to add a new calculation. We want to get the Rank of the each of the Customers for the sum of Sales. This is given by RANK(SUM(Sales)). We want to filter when the RANK is less than or equal to the number of Top Customers we want so the formula becomes RANK(SUM([Sales]))<=[Top Customers]. Finally drag this to the Filters pane, select to keep only the True values and we’re done. As this filter is applied after the percentage of total calculation, the percentages remain of the unfiltered set.

image

This is a really flexible way and is independent of whatever filters you want to apply. I have stuck these three example in a Tableau Public workbook.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s