I have a chart (not quite a dream), this chart is broken down by two dimensions ([Category] and [Sub-Category]), and a measure ([Sales]).
But when I sort sales I get a type of what I call, ‘nested sorting’, it is sorting sales, but it is sorting sales within each category (if you have a different number of dimensions, it will sort within the 2nd last dimension on your shelf.
But there is a way to prevent this, I simply need to concatenate the dimension members into one calculation, and place this as the first column, and the hide it.
To achieve this I create a calculated field, but you can achieve an identical result by combining your fields (I don’t know why, but I prefer the former).
Something that I often offer my users when such a requirement exists is a parameter that switches out the table with another sheet when they want to sort, using this great tip by Hashu Shenkar of The Information Lab.
I do this because I prefer the look and feel of the unsorted table, and how it doesn’t include unnecessary dimension members values as they are repeated down the table.