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.

Filter order

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.

Context

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.

Tinkering With Binning LOD Expressions

Like I said in one of my first tinkering blogs, a lot of what I learn is from tinkering around with some question I find on the Tableau Forums. I found this question today: I want to get count based on number of counts. To start with Ashish wanted to figure out how many medical tests each patient had been given. But as with many forum questions, it turned out to be a bit more complicated than that. What she really wanted to figure out was what percent of patients are given 1,2,3,4… tests as compared to the whole. So the assignment is:

  1. Count number of tests for each patient
  2. Bin the patients by total number of tests they’ve been given
  3. Calculate the percent of the total each bin contributes to the whole

In 8.2 something like this is more work than it’s worth (and there’s always that extra dimension that gets mentioned at the very end that mucks everything up). But with the new LOD Expression this becomes quite simple, and even a bit enjoyable. So let’s work through it.

First I want a count of the number of tests per patient. Since I only want the Patient ID to be considered when making the count, I know I’m going to use a FIXED LOD expression. So I created this calc:

{FIXED [Patient ID] : COUNTD([Test LIS Code])}

Now here’s the new bit I learned today: FIXED (and only FIXED) LOD Expressions can be binned! That’s right, we can now bin an aggregated dimension. So we can first aggregate (count) the LIS Codes, and then we can bin them into a bin size of our choice. How cool is that? How did I learn this? As with so many things, I right-clicked a field and looked to see what Tableau was going to let me do. And there was the Create Bins… option (well the Bins…  option in the new Create flyout menu).

Just to finish up, after putting the bins out onto the Column shelf (and adding a parameter to control the size) I just added a quick table calc for percent of total to the LOD calc. Anyway the workbook is posted here.

I hope you’ll enjoy your own tinkering with binned LOD expressions.