I am trying to create a chart that demonstrates the spread of peoples activity across multiple categories. Specifically I'm trying to create a stacked bar chart where each person is on a different row and within that row the bars break down each category, with the bars colours not to represent the category itself but the relative importance (ranking) of that person's primary, secondary and tertiary categories (etc.). I am not interested at all in what those categories are, just how much spend value each person 'spends' on their primary, secondary and tertiary categories. I want to see how many people spend in predominantly just the one category (whichever that may be), how many spend mainly across two categories and so on.
See attached example.
The logic behind this is simply:
1) Sum values per category per person
2) Rank categories per person
3) Plot stacked bars with the length of the bar being the sum and value, and the colour being the rank relative to that person
4) Order rankings so that the primary category (rank 1) is at the left, secondary category (rank 2) is next to the right, and so on.
I can manage all of this up to step 4 with simple table calcs, but I cannot figure out any way of getting the sort I want. Sorting by category is not an option as the global totals will be calculated and ranked accordingly, so a category which is of low importance to somebody but highest importance overall will be sorted to the top.
However, I need to use category in the detail shelf or the table calculation will not be possible. It appears that by introducing category here Tableau insists on a standard sort order for category across all people, which means while the colours and bar lengths are correct, the sort order is effectively random for what I'm trying to do. Specifically I would want the red bars to always been at the left, followed by the orange and then yellow.
I am hoping there is a solution involving modifications to the table calcs or sorting on some new calculated field. Any ideas?
Example.twbx.zip 16.5 KB