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.

Advertisements

10 comments on “Tinkering With LOD Expressions & Percent of Total

  1. swexler says:

    Have only just discovered your blog; great stuff.

    Like

  2. forzabarca says:

    Nice read, thanks Shawn

    Like

  3. Great post, Shawn. I really like this example and very thoughtfully explained. Thanks!

    Like

  4. Daniel Ström says:

    This was very helpfull and interesting Shawn!

    Do you know how to apply this over dates? (years, months…) tried that on your Brain Teaser workbook but never got it to work per date, only as total of all choosen dates.

    Cheers!
    Daniel

    Like

    • Daniel, if you want to use dates in an LOD expression, you just need to create a calculated field with the date level you want. So if you want to know how many orders per day, first create this calc:

      DATETRUNC( ‘day’ , [Order Date])

      And then use it in an LOD expression like this:

      { FIXED [Datetrunc Calc] : COUNT([Order ID]) }

      Does that answer your question? If not post your question on the forums and link to it here.

      Like

  5. Brisa says:

    How would this work with multiple filters? As of right now, I am looking at demographic data, specifically ages, and incomes of clients we serve. I calculate who is “work likely” by filtering for ages 24 – 61, and filtering for “Total SSI Income” = 0. I have four different offices these people can be associated with, and i got a great count of how many work likely’s we have going in to each office… now i want a percent, out of all the clients each office serves, what percent of them are work likely? but with the filter i have it doesn’t calculate the total number we serve… just the total work likely….

    Like

    • Brisa, you’ll be better served if you ask this question on the Tableau Forums That way you can post a sample packaged workbook to give us a better idea of your situation. Basically it seems as if you need to use a FIXED LOD for the ‘total number we serve’. This will execute before the filters so it won’t be filtered by the work likely filter (assuming you didn’t make that a context filter). If you do it as a FIXED without any Dimensions then you’ll get the total of everything in the data set. If you do a FIXED with [Client] and [Office] you will get the number of total clients by office. Anyway like I said, ask it on the forums and I can give you a better, more exact answer.

      Like

  6. Hi Shawn, thanks for posting the brain-teaser. I am trying to master the LODs and on the surface of it, it is very easy to understand. However, it took me a few minutest to get the visible totals for the denominator. Thanks for giving a nice little workout on LODs.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s