This should work:
I created a window sum calculation and computed this across segment. This returned the sum of segments per category and country:
Then ranked this window sum:
This now gives the correct sort order.
Next I created a window average of sales and computed this across category:
This can now be used to calculate you reference line!
Nested Sorting Problem.twbx 1.1 MB
Awesome Neil - Thank you so much!
Don't know why I didn't think of window calculations - it makes perfect sense!