Nested sorting in dimension hierarchies Tableau

Question

I'm working on a Tableau visualisation with a dimension hierarchy (Product category, product sub-category, product type, and so on) sorted by number of orders. I want my viz to show only the first product level (product category) sorted the same manner by default, but with the opportunity to dig down to specific product levels using nested sorting (using "+" on the dimension) (again, descending by number of orders).

data from a superstore

For each product level, I tried utilising the nested sorting option, however as I dig up and down again, the sorting is incorrect again, as if it clears out. I can't seem to discover a way to keep them fixed unless I keep all product tiers in the viz accessible (without drill-down option).

Is there anyone who can tell me how to do it? I also attempted several indexing and ranking calculations, but none of them seemed to work. I know there's a way to mix hierarchy dimensions and use the sorting option on them, however it makes the viz seem a lot messier.

Thank you so much in advance!

Vaani · Answer 1 · Mar 29, 2022

Tableau will always sort by the column on the left. You may conduct a secondary sorting more simply using the updated nested sorting. When you extend up and down hierarchies, as you are doing now, the formatting may not be preserved.

The "traditional" method is to construct a Rank based on the quantity of orders (sounds like you were close on this one). COUNT([Order ID]),'desc'), rank(COUNT([Order ID]),'desc'). Make this a distinct measurement and place it to the left of the others.

To clean it up, you can uncheck "Show Header" on the rank pill.

And if you expand/collapse the hierarchy, it keeps the sorting... Final product:

EDIT: Here is another way to try to accomplish this. It seems to work for 3 levels but starts to break down after that. (It also didn't seem to work well on grouped dimensions.)