Tableau Deep Dive: Table Calculations – Custom Sorts, Part Three

Data

Tableau Deep Dive: Table Calculations – Custom Sorts, Part Three

Tableau Deep Dives are a loose collection of mini-series designed to give you an in-depth look into various features of Tableau Software.

In the last post of our series on custom sorts in table calculations, we’ll examine one of the most common questions that I get from Tableau Training. In fact, I just had this question last week in Perth, Western Australia by a student named Mayank. Here’s the question:

“How do I sort on discrete measures?”

Often, we make measures discrete so we can include them in the header as a number and visualize a different measure in the chart. Let’s use custom sorts to make this work.

Sorting on Discrete Measures

I want to build a chart that compares sales and profit as marks but then sorts the table off a measure I’m making into a discrete header. How about a top 10 by profit margin? Let’s start with our bar-in-bar graph by comparing my sales as the underlying bar to profit as the top bar:

Sales and profit bar-in-bar chart

I’m going to add Profit Margin alongside the name of the country as a discrete header. It won’t appear as a mark, but that’s fine because anything else on my bar-in-bar graph would probably start to detract from the story I’m trying to tell with my data. I’ll start with my calculation for profit margin:

Profit margin calculation

Remember, on weighted averages we have to aggregate the field first and then do our ratio, otherwise we get a row-level calculation, which is going to give us the wrong number and perform slower. Now I’ll take our Profit Margin and drag it to the Rows shelf as a discrete field:

Adding Profit Margin to Rows

Profit Margin has AGG() around it. Tableau uses this designation to let us know that this field is already aggregated inside of the calculation. With a little bit of formatting on our font, shading and borders, we get this view:

Adding some formatting

I’d like to sort this table by the Profit Margin percentage, but I cannot use a conventional because it is not a measure in the chart. It is a part of the header. To tackle this, let’s create a new calculated field called Rank that just has the INDEX() function inside. By definition, INDEX() is a table calculation, which is why you see the “computed along Table (across)” and the rest:

Rank calculated field

Again, I want to make this measure discrete and add it to the Rows shelf, but this time at the very beginning:

Make discrete and add to Rows

I’m going to add some custom formatting to this field to add a # symbol in front of the number. For me, it just makes it very clear that it’s a ranking value. Right-click on the Rank field on the Rows shelf and select Format. In the formatting menu on the left, find Numbers and apply this format:

Custom Format

Now let’s add our custom sort to our table calculation which will allow us to use Profit Margin as the measure to sort our Country field. Right-click on your Rank field, select Edit Table Calculation to get into the table calculation window. Start by changing Compute Using to Specific Dimensions:

Compute Using Specific Dimensions

That will open up the option of a custom sort under Sort Order. Find Profit Margin in your drop-down list and select Descending to have it from highest to lowest:

Sort Order - Custom - Descending

Finally, drag a continuous copy of Rank onto the Filters card and select a range from 1 to 10. This is the finished view of our top 10 by profit margin custom sort:

Final Top 10 by Profit Margin custom sort

Add a Parameter!

If there’s a theme in my articles, it’s perhaps this: Tableau is the best BI tool on the market today and adding a parameter makes it even better! Let’s parameter-ize our custom sort so that our user can decide if they want to see a top 10 on Profit Margin (as a discrete header) or using profit or sales (as marks in our visualization).

Step one is to build your parameter.

Sort Table By (top 10) paramter

Right-click on your parameter and select Show Parameter Control. Now let’s create a calculated field that will utilize my report user’s selection from my parameter control. We’ll call it Sort Calculation:

Sort Calculation

All we have to do now is update any instance of our Rank calculated field to use the Sort Calculation as the field for the custom sort. Remember, we’ve got Rank in two places in the viz above – one of Rows and another in the Filters card. You’ll need to modify both.

Sort Order - Custom - Descending

Here’s our finished visualization, published out onto Tableau Public.

You could of course replicate this exact same functionality with multiple discrete measures as headers quite easily. Note, sunny Australia coming in on the top 10 on both sales (#2) and profit (#7). Aussie, Aussie, Aussie! Oy, Oy, Oy!

What’s Next?

Be sure to check back on the InterWorks blog for more content on Tableau and table calculations. Leave any questions or thoughts in the comments section below. Cheers! 

Want More Tableau Deep Dives

  1. Tableau Deep Dive: LOD – Introduction to Detail
  2. Tableau Deep Dive: LOD – The Include Calculation
  3. Tableau Deep Dive: LOD – The Exclude Calculation
  4. Tableau Deep Dive: LOD – The Fixed Calculation
  5. Tableau Deep Dive: LOD – LOD Calculations vs. Table Calculations
  6. Tableau Deep Dive: Parameters – Parameter Overview
  7. Tableau Deep Dive: Parameters – Parameter Properties
  8. Tableau Deep Dive: Parameters – Filtering – Top N
  9. Tableau Deep Dive: Parameters – Calculated Fields
  10. Tableau Deep Dive: Parameters – Filtering Across Data Sources
  11. Tableau Deep Dive: Parameters – Bins
  12. Tableau Deep Dive: Parameters – Reference Lines
  13. Tableau Deep Dive: Parameters – Table Calculations
  14. Tableau Deep Dive: Sets – Introduction to Sets
  15. Tableau Deep Dive: Sets – Constant Sets
  16. Tableau Deep Dive: Sets – Computed Sets
  17. Tableau Deep Dive: Sets – IN/OUT
  18. Tableau Deep Dive: Sets – Combined Sets
  19. Tableau Deep Dive: Sets – Calculated Fields
  20. Tableau Deep Dive: Sets – Hierarchies
  21. Tableau Deep Dive: Dates – Introduction to Dates
  22. Tableau Deep Dive: Dates – Preparing Dates
  23. Tableau Deep Dive: Dates – More Date Functions
  24. Tableau Deep Dive: Dates – Exact Dates
  25. Tableau Deep Dive: Dates – Custom Dates
  26. Tableau Deep Dive: Dates – Rolling Dates
  27. Tableau Deep Dive: Dates – Calendar Filters
  28. Tableau Deep Dive: Dates – Week-by-Week Comparison
  29. Tableau Deep Dive: Dashboard Design – Planning
  30. Tableau Deep Dive: Dashboard Design – Layout & Structure
  31. Tableau Deep Dive: Dashboard Design – Proof of Concept
  32. Tableau Deep Dive: Dashboard Design – Adding Interactivity
  33. Tableau Deep Dive: Dashboard Design – Visual Best Practices
  34. Tableau Deep Dive: Dashboard Design – Optimization & Governance
  35. Tableau Deep Dive: Dashboard Design – Publishing
  36. Tableau Deep Dive: Table Calculations – Custom Sorts, Part One
  37. Tableau Deep Dive: Table Calculations – Custom Sorts, Part Two
  38. Tableau Deep Dive: Table Calculations – Custom Sorts, Part Three

More About the Author

Robert Curtis

Analytics Consultant | ANZ Practice Lead
Pimp My Viz: Volcano Edition This is the first of a new series of blog articles called Pimp My Viz. It combines my passion for visual analytics and a desire the ...
Tableau 2019.3 Spotlight: Tableau Catalog In my earlier article highlighting the new features of Tableau 2019.3, I touched very briefly on Tableau Catalog. I’ll spend a bit more ...

See more from this author →

Subscribe to our newsletter

  • I understand that InterWorks will use the data provided for the purpose of communication and the administration my request. InterWorks will never disclose or sell any personal data except where required to do so by law. Finally, I understand that future communications related topics and events may be sent from InterWorks, but I can opt-out at any time.
  • This field is for validation purposes and should be left unchanged.

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK

×

Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Germany
Geschäftsführer: Mel Stephenson

Kontaktaufnahme: markus@interworks.eu
Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072