Tinkering With LOD Expressions & Percent of Total

If you are anything like me, you skim a lot of blogs about Tableau, note the gist of the post and leave reading the details for later. This morning I finally got to ‘later’ for Bora Beran’s excellent introduction to LOD Expressions, and I’m glad I did.

This image he posted caught my eye, stuck in my head, and really helped me get a visual understanding of what LODs are doing especially when it comes to Percent of Total calculations.

This is the order of execution of different types of filters, and where different types of LOD expressions fall into this order. Before we get to the percent of total stuff I want to note the caveat Bora mentioned that brought all this home for me:

Fixed calculations happen before dimension filters, as a result unless you promote the pills in your filter shelf to “Context Filters” they will be ignored.

Interesting. So I set up a workbook to test this. It used a FIXED level of detail calculation, something like this:

{FIXED : SUM([Sales])}

Then I created a map and filtered the viz to a map of three States. Since State is a dimension, and is filtering after the FIXED LOD, the returned value didn’t change when I added this filter. But then when I added context to the State filter, the filter jumped in front of the FIXED LOD and therefore changed the value being returned. Remove the context, and the filter becomes a Dimension filter again, and gets executed after the FIXED LOD expression.

Cool, right? I got to thinking that this is really what we need to do when calculating percent of total: have the calc consider a dimension sometimes, but then ignore it at other times. However we don’t currently have a way of allowing the end-user to toggle Context on and off. But do we really need that? Or can we just move down a level and setup calculations with some parts happening before the Dimension Filters and some happening after.

Of course reading further in Bora’s blog we get to exactly that same point. He writes:

Imagine writing the following calculation

SUM([Sales]) / ATTR({SUM([[Sales])})

and having the dimension [State] in your Sheet. With this setup, this calculation will give you ratio of a State’s sales to total Sales. You can add a filter to your Sheet to hide some of the States but filter will only affect the numerator. Since denominator is fixed, it will still give the sum for all States.

Rather than imagining I went ahead and set up that exact viz, and it worked as advertised. A percent of total, without a table calculation in sight! Now that is cool. But then I started thinking, wouldn’t it be even better if we could toggle between showing a percent of total for all states, and a percent of total for only visible state? After a bit of tinkering I worked out a solution, which tickled my fancy enough that I made it into an Educational Brain Teaser I posted on the forums. Go over there, and see if you can work it out, before reading further….

Spoiler Alert! Answer to the brain teaser is posted below….

Here’s the Mystery Calculation I used to create the toggle between percent of all and percent of showing (Select Percent Total is a parameter):

IF [Select Percent Total]=’Percent of All’ THEN
SUM([Sales])/ATTR({SUM([Sales])})
ELSE SUM([Sales])/ SUM({EXCLUDE [State] : SUM([Sales])} )
END

[NOTE: Learn this now, save some hair! Writing: {SUM([Sales])} is the same as writing {FIXED : SUM([Sales])} They return the same result. The first is a shortcut for the other.]

In the above calculation we are using two different ways of getting a percent of total. In the first: SUM([Sales])/ATTR({SUM([Sales])})  we use a FIXED LOD to get the denominator before the State filter takes effect. So we are dividing the total of all states into the sum of each filtered state that is returned. In the second half of the IF statement we are doing all our calculations after the dimension filter has been applied. But we still need to get a TOTAL() denominator to divide into the SUM([Sales]) numerator. To do this we use one of the other type of LODs: EXCLUDE. This tells Tableau to ignore the State field when calculating the SUM([Sales]), so we get a total of all states in the view (after the Dimension filter is applied).

Just study Bora’s order of filtering chart, work out in your head what gets totalled when, and you’ll master the new LOD percent of totals in no time.