Hi R. Sinclair, can you add the workbook with data? I'll check for you. I am still online so probably can do it pretty fast. Rgds, Arjan
If you unpivot your data it's easy (see attached). I don't know of any way to do it with Measure Names and Measure Values (I won't say you can't because Joe will just prove me wrong).
sort_problem_example_rl.twbx 71.5 KB
This seems like a duplicate post of http://www.tableausoftware.com/support/forum/topic/sorting-measurement-values-dynamically and Alex offered the same option as Richard, so I am guessing reshaping your data is not going to work for your situation.
So here is an alternate solution, and depending on your exact requirements, there are a variety of options. Attached is the basic route to get what you are looking for with a data blend and a parameter.
The primary data source I use is this:
Measure Sport Health Hobby Profession Income
Then create a parameter from the Gender field, adding an "All" option, and create a calc field for each of your measures in your provided data source like:
IF [Gender]=[Gender Parameter] OR [Gender Parameter]="All" THEN [Health] END
Then in the new Primary data source, create a calc field for each (this may not be a necessary step in all cases):
AVG([TEST DATA].[Health Filtered])
Then add a calc field like:
CASE ATTR([Measure]) WHEN "Health" THEN [Health] WHEN "Hobby" THEN [Hobby] WHEN "Income" THEN [Income] WHEN "Profession" THEN [Profession] WHEN "Sport" THEN [Sport] END
Then for sorting, create a calc field like:
and layout the worksheet as attached. You will get a message telling you there is no relationship for the data blend, but you can dismiss this message.
So you have a choice, either reshape your data for Tableau, or jump through a few hoops inside of Tableau. If you want something more sophisticated there are other options and variations of what I have described here. If anything, when it comes to Tableau, you have options.
sort_problem_example_edit.twbx 22.6 KB
Great solution Joe. Odd in a way that I can sort the fields manually, but cannot do it automatically without these hoops
It is my guess that the Measure Names/Measure Values re-structure of data happens after the data is returned from the data source, and the Sort dialog on a field's value is sorting in the underlying data source. So in my view it is an order of operations situation.
The choice is restructure before Tableau, and you can use the sort dialog on a field, or use a data blend like I have to enable the sort dialog on a field. Because the Measure Name concept happens after field value sorting, Measure Names is not enabled to be sorted on a field.
That does the trick! It's not the obvious fix I thought it might be ( i thought i was missing something simple) but it does achieve the results i was after.
Very much appreciated,
I was a bit too quick in thinking that my sort problem was solved. What modification would I have to make to your proposed solution, so that it would work when another worksheet is acting as a filter?
That is doable as well. you would need to generate a primary data source with one row for every possible combination of zip code and measure, like
Zip Measure 12345 Sport 12345 Health 12345 Hobby 12345 Profession 12345 Income 78945 Sport 78945 Health 78945 Hobby 78945 Profession 78945 Income 45612 Sport 45612 Health 45612 Hobby 45612 Profession 45612 Income
for 3 zip codes.
Then your relationship for the data blend will be on the zip code field.
Because you want an average for the values for all zip codes selected, we will have to perform the average later, so each of the fields that brings the measure across the blend will need to change from AVG to SUM, and we need another field setup like the others for Count
Finally you will need a table calc for the Average Value like:
IF FIRST()==0 THEN CASE ATTR([Measure]) WHEN "Health" THEN WINDOW_SUM([Health],0,IIF(FIRST()==0,LAST(),0)) WHEN "Hobby" THEN WINDOW_SUM([Hobby],0,IIF(FIRST()==0,LAST(),0)) WHEN "Income" THEN WINDOW_SUM([Income],0,IIF(FIRST()==0,LAST(),0)) WHEN "Profession" THEN WINDOW_SUM([Profession],0,IIF(FIRST()==0,LAST(),0)) WHEN "Sport" THEN WINDOW_SUM([Sport],0,IIF(FIRST()==0,LAST(),0)) END / WINDOW_SUM([Count],0,IIF(FIRST()==0,LAST(),0)) END
it may look gnarly, but is is just a window sum for each measure with a divide by window sum of count at the end, all wrapped in an iff statement to show one mark. When this pill is places on the worksheet, you will want to use the Zip code field for the Compute using setting.
Then you setup your action, using the Selected fields option, and choosing the new data source of Measure/zip combos as the destination data source.
and that is about it.
I did this in the attached.
sort_problem_example_edit2.twbx 77.6 KB
You are truly a Tableau Jedi. Thanks Joe
Hi Folks, I've read several different threads dealing with sort. It seems there are a couple of different solutions when it comes to dynamic sorts on multiple fields. Before I start down one of these rabbit holes, I could use some advice on which route to take.
The attached workbook contains a simplified version of a typical set of dashboards our company uses for media planning, in this case its a radio study. The sheet I'm interested in sorting is STLCH Radio Dash. While I don't need to sort the Day Part Study Dash sheet, it does show how the complexities can multiply fairly quickly. No matter what we deliver to the planners it seems they are always asking if we can add another dimension or measure, so the numbers of parameters will continue to increase and change over time. (There are endless categories when it comes to slicing and dicing audience/listeners, especially when you start adding in psycho-graphics.)
So I guess the question is what is the most scale-able and adaptable approach to take given this type of data?
sort_samples.twbx 203.9 KB