Maintaining percent of total whilst filtering
Tableau 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.
- Calculates SUM([Sales]) for each dimension member
- Applies a filter for Central and East
- Works out the total for the regions in view
- 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.
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$]”)
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
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 ?
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?
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.
This post (first method) just saved me doubling a huge dataset to get the same result. Thank you so much!
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.
Hi Rob. I have run into this same issue – correct percentage calculations for MSAS cube,. Were you able to solve your problem? If yes, how? I will be really grateful for suggestions on how to do this.
Regards,
Tapa
I had a similar situation in MicroStrategy Visual Insight and the “first” option helped a lot. I used min function.
Thanks
Ranjan
Thanks for another great post. I’m using Windows Tableau 8.1.7 and confirm that methods 1 and 2 are reproducible.
I have used method 2 before and like it because it’s easy and maintainable. I think method 2 can be operationalized without too much difficulty.
Method 1 is too Perl-like. But, I respect how amazing of a solution it is and admit I don’t understand table calculations too well.
I didn’t try method 3 because I probably would not recommend or try to operationalize it in production.
Hi Robin.
Thank-you for this helpful post. But my data source is MSAS cube, and these options will not work for me. Do you know how I can calculate correct percentages?
Regards,
Tapa
Found this (still quite useful!) post in a web search, I wanted to add that Tableau v9 adds another option using a FIXED LOD expression and a regular dimension filter. If I have SUM([Sales])/MIN({FIXED : SUM([Sales])}) then the FIXED LOD expression will be computed prior to any dimension filters, while the SUM([Sales]) numerator will be computed as an aggregate after the dimension filters.
Jonathan
Hi Robin,
Thank you for this post! it’s exactly what i was looking for
however, i must edmit i didn’t really understand how the calculated filter for the region works, and how it brings back the regions you wanted..
LOOKUP(MIN(Region),0)-> this looksup in the table the value brought by the MIN function on the region, and returns it, right? how is it selecting East & Central?
thanks
@Batchen – with Region in the view MIN([Region]) returns the given region for each Region. The LOOKUP(…,0) is getting the value of MIN([Region]) for each Region as a table calculation result, so it’s returning all the regions. Then if we only pick a region or two to show, that filter is applied after the other calculations are done.
The fixed LOD expressions in the newest versions are much easier now to use as Jonathan already mentioned!
Hi ,
we can do this with LOD Calculation easily , create a dummy filed like
calculation s having ‘s’ like dis , so this calculated column s will have value ‘s’ to all rows in our data. Then create a calculation named Total_Sales as below.
{ FIXED [s] : SUM([Sales])}
then create our final calculation as Sales_new as
SUM([Sales])/SUM([total sales])
then us this column in the view.
Yes, the inclusion of LODs from Tableau version 9 has given yet another way to solve this. It would be simpler to just use {SUM(Sales]} though, rather than creating the dummy field as you suggest. Be aware the FIXED LOD expressions ignore filters, unless they are made to be Context filters.
Excellent thankyou
Question please: Why cant a Fixed type LOD not do the job ! I tried but it still seems to be affected by a dimension filter. I thought in the documentation that Fixed is independent of dimension filter. This should have fixed the issue very easily. Pity
Ali, a FIXED LOD should work well here. FIXED LODs will be computed before any regular dimension filters (but not before Context filters)
Does anyone have any suggestions on how to do this when you want a reference line per pane cell instead of per pane? Thank you!
Thanks, the first option is exactly what I was looking for!
However, I need to use the same filter for multiple sheets in my dashboard, and when using the table calculation as a filter, I don’t have the option of applying the filter to other sheets.
Do you happen to have any way around it?
(Also, I tried the solution suggested in the comments with LOD expressions, but I’m using Exclude since I need all filters to apply except one, and couldn’t make it work with Exclude. Is it working only with Fixed?)