Now with video 🙂
Last week I ran into an interesting situation with Tableau. I wanted to sort dimensions within larger dimensions by a measure. After that sort, I wanted to encode an additional dimension on color. Here’s what that would look like using Superstore:
In the view I am looking at sub-categories by each segment, hoping to rank them by the sum of Sales. I’ve encoded an additional measure (discount) on color.
This could be a great visualization for understanding demographics within hierarchical type dimensions. Like say the gender breakdown of who has diabetes at hospital A.
The issue is, getting to the view shown above is somewhat more complex than I had originally thought. So let me walk you through what happened.
- Set up my view of Customer Segment, Sub-category, by sum of sales
- Created initial rank calculation (index()) and then did the typical sorting.
- Table calculation is set as follows (Custom Sort = sort by Sum of Sales, descending order):
4. Gets me here:
5. Now when I add Discount to color, my whole viz breaks:
6. To correct this a few things have to be done. Initial table calculation needs to be modified to ensure the Discount is taken into consideration, but not considered for the sorting:
Super important to notice here that Discount is at the lowest level, but we’re computing at the “Sub-Category” level. We’re still restarting every “Segment.”
That gives us this:
So now we have the sub-categories correct, we’re looking by region. But we’re back at that original point of our sort isn’t computed correctly. This is because we’re sorting by the highest sum of sales for a given discount in a segment. The first sub-category is found and grouped together. Then the next sub-category is found with the next highest (not 2nd, just ‘next’) sum of sales for a given discount. Check it out in comparison to the crosstab, the pink highlights how the index() is working:
To fix this last step, we need to let Tableau (the table calculation, the world!) know that we don’t care about discount for the sum of sales. We only care about sub-category and segment. To resolve let’s pull in a simple LOD:
Now finishing it all up, replace the measure used in the table calculation for sorting:
And we’re back at what we originally wanted:
Full workbook with story point walk-through here: https://public.tableau.com/views/LearningMoment-sortingwithtablecalculations/Learningmoment-tcalcs?:embed=y&:display_count=yes