thanks a lot for looking into it! I think your solution is not exactly what I wanted:
You created a sort in ehich each bar is separately sorted based on the state-specific quantities.
the states to be sorted on the highest % of furnishings (being the largest sub category overall) (eg Maine and Montana on the left, Wyoming & Vermont.
Ideally, If two states both have no furnishing, they should be sorted on the second largest overall quantity (chairs) etc.
Each state should be sorted bottom to top: furnishing -> chairs -> tables -> bookcases
so in the left bottom corner we will see a lot of pink color, disappearing through other colours to purple in the top right. WHen selecting a different category, the same should happen for the new selection.
Hopefully there is a nice way to do this!
That looks more like it,
this calculated field is not dynamic to the selection of the category. It should automatically calculate whis sub category is the largest overall and use that as a sort...
it seems that not all states are sorted with the furnishing first (being the largest in quantity overall).
ALso, somehow i cannot sort on calculated fields.... They do not appear in the 'sort by' 'field' options when i right click on sort in the marks.. How can this be done?
So more questions :-).. Nice challenge... Thank for thinking along!
As for the second part of your question - the sort within each state is still due to the combed pill I left on the detail shelf. Once removed all segments within each state will be same order. Also you cannot sort by calculated fields that are TABLE calcs but you can sort by other measures that are calculated fields.
I'll have to ponder that later on when I get a chance to get back to it.
thanks for your help so far, You brought me closer to the solution!
Hope you find time for further pondering :-).