Over at Peter Gilk’s Paint by Numbers blog there was a question on this post on filtering while retaining results. Here’s the what Jeremy asked:

May I ask if it would be possible to get a detailed explanation of applying this principle to a different type of data?

For example, I would like to see the US Sales totals, and have the ability to filter it to a US state without the ability to select a US territory (Guam, Puerto Rico, etc), but to have the US territory sales remain in the US national totals. How could I do this?

In this short post I cover two different techniques how to do this using a self-data blend and LOD expressions, respectively.

The basics are all covered in LOD Expressions and Custom Grand Totals, in that post there are two solutions to the problem of getting a % of total in both the detail rows and Grand Total whose denominator (the total sum of sales) doesn’t change based on filters, here’s the blend view:

Using Customer Segment as a stand-in for the states & territories from Jeremy’s request, I’m going to phrase the goal now as to exclude Small Business from the list of filterable Customer Segments whilst including Small Business in the total for the % of total. We can get that in either the blend or LOD versions in 8 mouse clicks. Here’s how:

1) Right-click the Customer Segment and duplicate it. Tableau will create a Customer Segment (copy) dimension.
2) Drag the Customer Segment (copy) dimension onto the Filters Shelf.
3) Choose Small Business, then click Exclude and OK. This removes Small Business from the view in the detail rows but not the Customer Segment Quick Filter:

4) Click on the Customer Segment Quick Filter drop-down, then choose “Only relevant values”.

The Quick Filter is now limited by the exclude filter on Customer Segment (copy) and shows only the three remaining Customer Segments while the total sales is unaffected in both the detail rows and the Grand Total:

These same 4 steps will also work for the LOD view:

If you’ve got other tricks for LOD expressions and filtering, I’d love to hear them in the comments below! Here’s the LOD and Custom Grand Totals workbook on Tableau Public.