Maintaining percent of total whilst filtering

Posted by on Jan 14, 2013

Result of applying quick table calcTableau makes it really easy to switch from showing absolute figures to a percent of total figure by using the Quick Table Calculation option. However, if you want to filter out one or more of your dimensions, the percent of total figure changes because the ‘total’ which is used in the computation changes too to reflect the loss of the dimension members. In some cases, you will want to maintain the original percentage (of the whole underlying data) whilst just displaying the dimension members you are interested in. This post examines 3 different ways to accomplish this, along with why you would or wouldn’t use that method.

Filter using a table calculation

Let’s just very briefly go through an example of what Tableau is doing in the background to get you your percent of total result. Consider a simple example using the sample Superstore Sales dataset where you show the percent of sales per region, just for Central and East.Oh dear that's not right

  1. Calculates SUM([Sales]) for each dimension member
  2. Applies a filter for Central and East
  3. Works out the total for the regions in view
  4. Divides each sales figure by the total worked out in step 3

Note that the Table Calculation is the last item performed in the list of steps. Table Calculations are always performed after the results from the standard calculations and filters have been computed. Therefore if you apply a filter directly to region, the ‘total’ will never equal the sum of everything over all regions.

Now it works!One way round this is to make Tableau apply the filter later, at the same time as the percent of total calculation. How? Make the filter itself a table calculation! By using the LOOKUP formula, we can make a new calculated field for Region that is used for filtering purposes

LOOKUP( MIN( [Region] ), 0)

Now use this calculated field on the filter shelf instead of the dimension Region itself and you will get the result you need.

This option is quick and easy to implement but it has limitations e.g. you cannot make this filter global so it may not be an option if this view and filter is needed on a dashboard

Duplicate your data

A second option is to create a duplicate data source which you will reference in a new percent of total calculation. Since filters only ever apply to a single data source at a time, a secondary set of data will be unaffected by any filters applied to the primary.

To accomplish this, right click on your data source and select Duplicate (it’s a good idea to call it something sensible such as ‘Totals’). Create a new calculated field in the original data source with the formula

SUM([Sales]) / SUM([Totals.Sales])

The last step is to remove the relationship between the two data sources for that dimension.

Data > Edit Relationships > Custom > [remove ‘Region’]

This method is useful when the first suggestion is not an option, but if you are working with large sets of data then it can potentially further clunk up your workbook

Use Pass Through (RAWSQL) functions

The final option to mention here is to make use of the pass through functions in Tableau. These functions send a query to the underlying data source, regardless of what filters have been used in the view, and return the result in a field to be used in Tableau. In this case, we want the sum of all the sales from the Orders tab in the spreadsheet.

RAWSQLAGG_REAL(“select sum([Sales]) from [Orders$]”)RAWSQL formula

This field can then be used as the divisor in any percent of total calculation

One disadvantage of using this method is that if you later switch to using an extract, the pass through function will break.

So there you have it. 3 different ways to maintain a percent of total calculation when using filters. If you know of any more ways, or want more help on this topic post a comment below or get in touch at info@theinformationlab.co.uk

6 Comments

  1. Hi, thanks for the useful info! This has almost but not quite solved my problem. I’m running into trouble with the first option you present: “Filter using a table calculation”.

    In your example you’re working with the Region dimension. In my case, I’m working with “Event Year”, which has values of 1999, 2000, 2001, etc.

    The problem is that the new calculated field that I create to then use as a filter cannot be converted from measure to a dimension. And it cannot be added to the worksheet as a Quick Filter.

    I believe the reason is because of the MIN() function within the LOOKUP formula.

    Even while it isn’t yet working for me, this info has been súper helpful. Thank you!

    Any ideas ?

  2. Hi Keith,

    You won’t be able to change it to a dimension because it is aggregated, as you have spotted, using the min() function.

    This shouldn’t stop you adding it to filters though. Try adding it to detail first and then right clicking and selecting Show Quick Filter?

  3. Hi, Robin.

    Great post, and thank you. We have been searching far and wide for a solution similar to the one you provide here, though our application would be somewhat nuanced. Our specific issue is that our salespersons have individual customers assigned to them. These sums of individual customers will change from month-to-month. For instance, this month our salesperson, Kline, has 546 customers. Last month she had 530 customers. Next month she may have more or fewer individual customers assigned to her; the same goes for her peers. Our workbook is arranged in the following order: [Month,Year], [Salesperson], [Showroom Location], [Distinct Count of Customers] (based on their unique customer identifier; this is to tell us how many individual customers we have in a month), [Count of customers] (this is to tell us how many individual customer visits we have in a month). We want to be able to keep the same order, but for our [Distinct Count of Customers] we want to have the monthly grand total to divide by. At this point, however, the [Distinct Count of Customers] is being divided amongst the salespersons by showroom.

    I hope this is making sense. Any assistance would be appreciated.

    Cheers,

    Richard

    • Hi Richard,

      It sounds as though you need to change the way the TOTAL table calculation is being computed… Right click on your % measure pill and select Edit Table Calculation. Under the Compute Using section, chose advanced and then put all the dimensions on the right hand section (Addressing) and keep your date field on the left.

      If that doesn’t quite work (without seeing your workbook it’s a bit of a guess), try a different set up using these options — you’re changing what elements of the view are being used to calculate that “total” — total of the whole table, total per salesperson, total per date etc etc.

  4. This post (first method) just saved me doubling a huge dataset to get the same result. Thank you so much!

  5. Hi there, how do you accomplish the same thing if your data source is SQL Server Analysis Services. None of these would seem to apply if you are using MDX rather than SQL…any suggestions would be appreciated.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>