Calculating Top and Bottom N% of Customers

Today, we will be calculating top and bottom N% of customers.  We had a similar post about this back in June, Top N% Filters in Tableau.  However, this post has two purposes.  First, we cleaned up the display and functionality to be more enticing to business users.  Second, we would like to raise awareness about our webinar series on Table Calculations in Tableau.

The third and final webinar in the series will be held Thursday, August 29 at Noon EDT.  You can sign up here.  The webinar will focus on using Table Calculations to answer any questions we can come up with.  Feel free to submit scenarios to us via email.  The only requirement is that the scenario be able to be replicated using the Superstore Sales sample data set in Tableau, which is also what we will be using for this post.  This example appeared in the last 10 minutes of so of the session 2 webinar, which can be found here (once it gets published).

Step 1:

• Create a chart of SUM( [Profit] ) per [Customer]
• Sort the [Customer]s in descending order of SUM( [Profit] )

Profit by Customer

Step 2:

• Calculate each [Customer]'s rank in the ordering, i.e. the index.
• Create the following calculated field.
• Force "Compute Using" to use [Customer]

Customer Rank

Now that we know what each customer's rank is, we need one other value to calculation top/bottom N%.  We need to now the total number of customers.

Step 3:

• Calculate the total number of [Customer]s
• Create the following calculated field.
• Force "Compute Using" to use [Customer]

Number of Customers

Now, let's make sure that these calculations are working as we intended.  The Labels Shelf is great for this.

Customer Ranking Test

These values are exactly what we were looking for.

Step 4:

• Create a parameter to store our N%.  For ease of use, 5% should be 5, not .05.  We will handle the mathematics inside the calculation.

Top/Bottom N%

Here, we could just calculate a simple True/False filter for the chart.  However, let's get slightly more advanced.

Step 5:

• Create a field that labels the Top N% as "Top N%" and the Bottom N% as "Bottom N%", with the value of N placed into the field.
• Create the following calculated field.

Top/Bottom N% of Customers

This field may look complex, but it only does two things.  First, if the customer is in the Top N%, it labels them "Top N%".  If that fails, then if the customer is in the Bottom N%, it labels them "Bottom N%".  The rest is just syntax to accomplish this.  You may wonder why there is a blank space before Top, but not before Bottom.  This is due to the fact that there are only two ways to sort string fields in Tableau, data source order and manual.  The data source order in this case is alphabetical, which would put Bottom before Top.  Therefore, we added a space to make Top come before Bottom.

Step 6:

• Add this field to the Rows Shelf before [Customer]
• Filter the chart using this field

Be careful when you do this.  Since the labels change via a parameter, you may run into issues if you choose to filter on the fields directly.  Instead, choose to exclude NULL.  This is also the same reasoning why we couldn't use Manual Sorting to place Top before Bottom.

Top/Bottom N% Filter

Finally, you have a pretty chart which shows the user which Customers are profitable and which ones are not.

Top/Bottom N% Chart

There's so much more to do here.  Try allowing the user to filter for Top 2% and Bottom 1% (Hint: use multiple parameters).  We hope you found this informative.  Thanks for reading.

P.S.

Remember to sign up for the webinar!  There will be plenty of cool stuff to see.

Brad Llewellyn

Associate Data Analytics Consultant

Mariner, LLC

llewellyn.wb@gmail.com
https://www.linkedin.com/in/bradllewellyn

3-Part Webinar Series on Table Calculations (From Beginner to Advanced)

I have begun creating a webinar series revolving around using Table Calculations to allow for much more complete and accurate analyses.  The information can be found at the following link:

https://clicktoattend.microsoft.com/en-us/Pages/EventDetails.aspx?EventID=171750

The first webinar has already been completed.

The second webinar will be held on Thursday, 8/22/2013 at Noon EDT.

The third webinar will be held on Thursday, 8/29/2013 at Noon EDT.

The videos for these webinars can be found On-Demand at:

http://www.mariner-usa.com/experience/videos/

Thanks,

Brad Llewellyn

Associate Data Analytics Consultant

Mariner, LLC

llewellyn.wb@gmail.com
https://www.linkedin.com/in/bradllewellyn