VizWiz

Launch, grow, and unlock your career in data

September 18, 2013

Tableau Tip: Sorting an "Other" dimension member at the end of a list

8 comments
Suppose you have a dimension, Subcategory in this case, and it has a member with the name "Other".  "Other" represents the bucket that holds all of the members that you're not interested in breaking out separately.

There are times when you would like to sort the Other member at the bottom of your list.  For example, you might have a bar chart of sales by subcategory that you want to sort in descending order, but you want to show the individual subcategories followed by Other at the end.  Typically, the bar chart would look like this (I'm highlighting other to make it easier to track for this example):


So how can I get Other to be at the bottom of this list?  Simple, create a calculated field that changes "Other" to negative for the measure you want to sort by.


Now, right-click on the dimension you want to sort, choose Sort, then in the Sort by section, choose the calculated field you created in the step above and sort in descending order.


And as easy as that, you have Other sorted at the bottom.


Note: This does not work for groups because Tableau does not allow you to leverage groups in calculated fields.

8 comments :

  1. Your approach here only works when all values are positive. What if your data looked like:
    Item Value
    A 10
    B 2
    C -5
    D -12
    Other 5

    Another option would be to have a calculated field like:
    IF [Item]="Other" THEN 2 ELSE 1 END
    and have it as the first pill on the Rows shelf as a discrete with its header not shown. You can then adjust the Level of the Border Format to the left to remove the extra lines.

    ReplyDelete
    Replies
    1. Ah yes! Great catch! Although I would expect Sales to be positive and there would be another measure for returns. But then again, possibly not. :-)

      Delete
  2. Since this is a dimension, wouldn't using the manual sort in the second screen shot be sufficient?

    ReplyDelete
    Replies
    1. Manual sort works until you filter or change something in the view, then everything is out of order again.

      Delete