Client requests are sometimes the best way to think outside of the box with Tableau. Sometimes you ask yourself “WHY?” and shake your head. Other times, the requests force you to come up with a creative new solution within Tableau that you’ve never come across before.
I recently had a request where the client wanted to see the top 10 followed by All Others with a Percent of Total. All Others simply aggregates everything that's not included in the top 10. I have seen reports like this in the past, but never have I seen or built one in Tableau myself. Below are the steps I took to achieve a top 10 and all others type of report.
Note: I was using the Sample Superstore English Extract for reference.
The final report looks like this:
First, create a view of Items sorted by Sum of Sales just to get yourself a visual. Next, Create a Set for the Top N Items by Sum of Sales. Add it to the front of the Rows shelf if you want to see the below.
TOP N parameter is just an Integer Type with All selected.
Next we are going to create a new Top N and All Others Field to group everything outside our Top N into an Item Called Various. Replace your original Item Pill with this new Dimension
Next, we are going to create a Rank calculated field that will Display our Top N and All Others grouped together as one. Place this on the Rows in front of your Item Pill, and set to Compute by Table(Down)
We now have a report that looks like this:
To get the final report for Percent of Total, simply change the Sales pill currently in the view to a Percent of Total, and add SUM(Sales) to the view again to get both % and SUM in the view.
If anyone has solved this in another way, feel free to shoot me an email at: email@example.com.
Thanks for reading Tableau Tuesday.