Can you not sort the Country by using the % calculated field?
Right click on the Country pill and click Sort. In the Sort options there is a choice to Sort by Field. Choose the % calculated field and that should sort how you want. Hopefully I'm not oversimplifying things...
If you put the Country dimension to the left of the IsUnknownVehichles measure (?), then do what Andrew mentioned, the sort should work. If you leave the dimension to the right of the measure it won't sort at all, as you've discovered. I've attached a Superstore sample.
Sort Problem.twbx.zip 1.1 MB
Thank you Shawn and Andrew for helping out. The problem with my view is that I cannot use the field "Number of Records" as my sorting field.
Please see below the full view. I am displaying hidden data and header and also added a "Number of Records" to illustrate my point.
What I want to do is to sort the "Countries" based on their respective percetange of "Known" values.
Since that percentage is calculated as a ratio between known and unknown within each country, I cannot use the field "Number of Records" in my sorting field.
I should also probably have mentioned more clearly that this "% measure" is not a "calculated field" per se but rather a "Quick Table calculation" based on the measure "Number of Records" expressed a "Percent of Total" computed using the field "IsUnknownVehicles" as shown below:
Sorry if my original post led to confusion. I hope the above makes sense. I'm sure I am missing the point somewhere but really can't figure it out. Thank you for your help.
Hi All. Could someone be kind enough to help? This issue is driving me nuts...
Thanks a lot!
You'll need to single out the 'Known' ones to be able to sort on it.
See the attached workbook (pretend that prod category is your known/unknown ones)
* sheet 1: everything sorted on furniture % (could be your known %) --> see calculation1
* sheet 2: dynamic with parameter --> see calculation2
Hope that helps
Thank you for helping out. Unfortunately, my issue is different.
Taking your format as an example, I would like to be able to sort in ascending order the list of STATES based on % value of FURNITURES (for instance).
As you can see the screenshot below is sorting the list of STATES based on the NUMBER OF RECORDS. But what I need is to sort them based on the "% OF TOTAL NUMBER OF RECORDS".
If I click on the small sort button that appears when I hover on the horizontal axis, the sorting works. See below:
The problem is that such sorting is based on the % values at the time. When the database is updated, it won't automatically sort my view again.
This issue is really driving me crazy.
Would really appreciate if someone could help me solving this...
1 of 1 people found this helpful
I'm just about to run out of the door so can't play with this as much as I'd like, but here's the approach I'd take:
- Make a copy of your Table Calc (Sum([Number of Rows]))
- Convert it to Discrete
- Drop it in front of [State], where it will force-sort the State values
- Hide the headers
The issue I'm seeing is that I'm getting an ascending sort vs. a descending sort...but I have the "auto sort" behavior you're looking for....Will play with this more later in the afternoon (my time) if someone else hasn't figured it out by then:
Thanks a lot for helping out Russell.
Do I really need to use a parameter to make this work? I have a lot of views (using different dimensions) for which I would require this auto sorting and that would be a real pain to implement it everywhere. Also, I anticipate that the non dynamic nature of parameters may create issues.
Your first suggestion (duplicate calculcated field and converting it to discrete) is a nice and quick workaround but as you pointed out it will sort in ascending order. Any way to change it to descending order while keeping it as simple?
2 of 2 people found this helpful
Yeah - the sample I attached earlier demonstrates same. Just change the expression in the discrete calculated field to [whatever the expression was, I forgot] to ([whatever the expression was, I forgot] * -1 )
Hackolicious, but it works.
Thank you Russel! Hope Tableau 8 will bring the ability to set this kind of sorting by default