As far as I know, it is not possible to achieve what you want directly in Tableau as the sort settings are uniformly applied to the entire view. However, there is another "out-of-the-box" way to achieve what you want. Instead of creating this as one view/worksheets, create 2 worksheets and then combine them into as dashboard. Then, you can have 2 different sorting criteria applied to the the 2 views in the worksheets. However, this is more work for you as you have to duplicate the worksheet and apply a filter only to show the IN values in one sheet and OUT values in another sheet. Please let me know if this would work for you.

Hi Carlos,

How's this:

Here's what I did:

1) Changed Subset Labels to be an measure using the ATTR() aggregation. This isn't strictly necessary for the view, I find it helpful to have as few dimensions in the view when using table calculations.

2) Created a Sort calculated field with the formula:

IF MIN(IF [Top N Reps] THEN 1 END) == 1 THEN

    -SUM([Total])

ELSE

    -WINDOW_SUM(SUM([Total]))

END

This will have a Compute Using on the Rep, so it partitions for each combination of values of Region and Set. This returns a set of numbers that Tableau's default sort for measures will return in the proper order.

3) Put the Sort calculated field as a discrete (blue) pill in the view prior to the Rep, so the reps are now sorted.

Jonathan

Jonathan, this is brilliant! As Carlos has asked for more explanation, I would also ask a little bit more explanation as to how the sort field works.

I was thinking it would be nice if Tableau allowed a formula in the sort dialog box as it allows in the filter dialog box. But you have provided a clever workaround using calculated field - I need to master table calculations a little more to fully grasp your answer. Thanks a lot Jonathan!

Thanks for the appreciation and the compliments...here's a more detailed explanation.

A regular dimension pill can be sorted manually, alphanumerically, or via an aggregate measure. Aggregate pills and table calcs only have two options: alphanumeric or manual. I set up the measure to return the negative sum of total because Tableau's alphanumeric sort is an ascending sort, that way bigger values will be turned into bigger negative numbers and therefore be earlier in the sort. We can see this if I turn off Show Header for the Sort pill:

Now about how I set up the calculation. First of all, we need to start with the level of granularity of the view. There are three dimensions in the view, the IN/OUT of the Set, The Rep, and the Region. So the SUM(Total) is being summed up for each distinct combination the values of those dimensions, making the bars. To get the desired sort, we need to return the SUM(Total) for each distinct Rep&Region in the Top N Reps of the Set, and return the SUM(Total) across all Reps for each Region in Other of the Set.

When we want to have a calculation that goes across a set of marks in the view, that calls for table calculations, a data blend, or custom SQL/custom query/view. In this case I chose a table calculation, here once again is the formula for the Sort field:

IF MIN(IF [Top N Reps] THEN 1 END) == 1 THEN

    -SUM([Total])

ELSE

    -WINDOW_SUM(SUM([Total]))

END

Because the result of the field is an aggregate, the IF statement has to be evaluating an aggregate. Tableau does this so that the user intention is clear. Now, the [Top N Reps] set evaluates to a boolean True/False, and Tableau won't let us just do a MIN([Top N Reps]). So I did a row-level IF [Top N Reps] THEN 1 END inside the MIN to get around that.This calc only returns 1 for those records that are in the Set, and Null for everything else. Therefore, the operation is "If (for the level of granularity of the view) the MIN of (IF [Top N Reps THEN 1 END) is 1 then return the -SUM(Total) (for the level of granulairty of the view)". This what gets us the -3109 and -3102 for the first two rows in the screenshot.

Now for the ELSE statement, which will be used for the Others part of the Set. The goal for these rows to return the SUM(Total) for all Reps for each Region. Since the Rep is in the view, that means we need to aggregate those SUMs and I chose to use the WINDOW_SUM() table calculation. This calculation sums up the aggregate measure across all of the addresses aka rows in the partition for each partition. The Compute Using (addressing) of the table calculation is set to be on Rep, so the calculation partitions (restarts) on each new combination of Region & Set. So, what that calculation does is that for each Rep, it sums up the SUM(Total) for all Reps within that particular Rep's Region & Set. This then returns the same result for all Reps in each Region & Set, and can then be used as a sort field.

One optimization that I often use in cases like this is to wrap the WINDOW_SUM() in a PREVIOUS_VALUE, like this:

-PREVIOUS_VALUE(WINDOW_SUM(SUM([Total])))

PREVIOUS_VALUE is Tableau's only real iterative calculation, it returns the result of this table calculation from the previous address aka row in the partition to the current address, and takes as an argument a value for the first address (Rep) in the partition. In the original calc, the WINDOW_SUM() is re-computed for every address (i.e. every Rep). If you're computing only a dozen WINDOW_SUMs, that won't be noticeable. However, if you're computing 50K or 500K WINDOW_SUMs, the performance hit will be quite noticeable. Using PREVIOUS_VALUE() causes the inner calc to only be evaluated once for each partition, and just returns that result to every other address (Rep) in the partition.

Table calculations are wonderfully powerful and not the easiest thing to learn, here's a page with links to explanatory pages:  http://drawingwithnumbers.artisart.org/want-to-learn-table-calculations/

Let me know if you have any questions!

Jonathan

Wow Jonathan! Thank you so much for taking your time to write such a long post! I feel so grateful to see such willing help in this forum. I will read and digest it and post here if I have follow-up questions.

It is almost as if you have read my mind. I was thinking of posting in the forum asking for advice on the various links to master table calculations as this is an area I really want to master. I attended the Tableau advanced classes in Geneva with high expectations that I can really grasp table calculations, but the class turned out to be quite a disappointment as the instructor himself did not understand table calculations well. None of the books really cover it deeply. So, your collection of links has provided me food for weeks, if not months. Thank you so much again and have a nice day!