Update Dec 14th: It looks like the INDEX() solution provided previously was not working after Datasource Refresh as Tableau defaulted to the Manual Sort. Tutorial updated with new approach that addresses this problem.

 

Have you ever tried filtering or sorting using fields with table calculations in Tableau? Right, it looks like it’s impossible, but with simple trick you can bring additional functionality to your dashboard.

Let’s use EU Superstore sample provided by Tableau and calculate sales share across Regions. Let’s say we are interested only in the best region (Top 1). There are 2 simple ways to display share across the regions:

1. pull green sales pill to the label in the marks section and then from the pill menu select Quick Table Calculations > Percent of Total
2-or-
2. Create a calculated field, let’s call it Sales Share, with formula:

sum([Sales]) / total(sum([Sales]))

3

Note the Total() formula – this will allow shares to be calculated against whole sample. Without it shares will always be equal to 100% for each of the Regions.

Having shares calculated properly, let’s filter the top one. To do so, right click on the Region pill and Filter > Top > By Field

4

As you can see there is no way to select sales share field in the menu. There’s also no way to select table calculation for Sales field. In this case, selecting Top by Sales would normally solve the problem but in the more complicated cases, where calculated field uses more than 1 field or you want to use sub-region, it would be impossible to use only 1 variable. You may also note that Sort menu for the region is also affected by this issue.

Sorting data

To show TOP N records, table must be sorted first. The easiest way to do so (as Sort Dialog is not working) is to use INDEX() RANK_UNIQUE() function. Create another calculated field which will generate Discrete version of Field with Table Calculation:

[Sales Share] * -1

This step is optional for displaying top N records – it’s essential however, to show TOP N records in sorted manner.

Place newly created field as first on the rows shelf. You should note that data should resort. You can control ascending / descending sorting by changing -1 to 1 in the calculated field definition.

post_20161214

Currently Region is sorted by Datasource order and not manual. This should allow datasource to be refreshed (and therefore new Regions to be added) and retain sort order

post_20161214_2

To get rid of the SalesShareSorted field right click on the pill and deselect Show Header. Don’t remove SalesShareSorted from the Rows shelf as it will mess up with the sorting after next datasource refresh.

Displaying TOP records

With sorted data it’s time to display top records. Create new calculated field or start typing in the Rows shelf:

RANK_UNIQUE([SalesShareSorter],'asc')

Pull newly created Continuous (Green Pill) field to filter shelf and select range of top items you would like to be displayed

Alternative solution

There is also an alternative solution however this should be used with caution, especially when connecting to database sources. Starting from Tableau 9.0, in calculations, your can refer to different level in the measure hierarchy using LOD expressions. For more details refer to Tableau knowledge base article here.

Using Level of Detail expression, you can mimic table calculation. To achieve the same we did in our example, create Calculated field called Sales Share LOD with formula:

sum([Sales])/ sum({fixed [Number of Records]: sum([Sales])})

now, this field is usable in sorting and Top options for the Region measure.
8

Note that calculation is bound to Number of Records Measure. It means that Tableau engine will scan the whole table every time calculation is updated. When having multiple connected datasources, this will mean serious querying time and sometimes can even cause SQL limit exceptions. That’s why it is strongly advised to use first solution instead of LOD in this case.