Extending LOD Calcs with Table Calcs

• By Joshua Milligan
• Posted in All, Calculations, Level of Detail Calcs, New Features
• On June 2, 2015
• 17 Comments.

I’ve been following with great interest the discussion in the Tableau community surrounding various approaches to using Level of Detail Calculations (LOD calcs) to solve various problems.  On the one hand LOD Calcs are often very easy and intuitive.  For example, it easy to determine an aggregation at any level of detail:

• How many unique customers per product?

{FIXED [Product] : COUNTD([Customer ID])}

• Maximum sales of a Category per State?

With State in the Viz Level of Detail: MAX({INCLUDE [Category] : SUM(Sales)})

 

But what if I want to know what the maximum category actually was?  That’s a little harder.  Mark Jackson recently posted on how to find a dimension at a lower level of detail along with a follow-up post based on an approach suggested by Joe Mako.  Jonathan Drummey had a similarly outstanding post which demonstrates his thought process in tackling such a problem.

What I’d like to do here, is replicate my thought process in solving a similar problem.  M thinking has been very much shaped by interactions with Mark, Joe, and Jonathan and my approach is very similar – but I find it very useful to see the slight variations in the different ways individuals approach similar problems.  And I’m going to extend the results a little to explore what kinds of analysis are possible after solving the initial problem.  I very much welcome any feedback!

 

The initial problem

Let’s say I want to know the Category with the most sales.  We’ll start by assuming I want to know the highest selling Category in a Department.  But later, I might want to swap Department for any other dimension (such as State or Region).

(follow along with this workbook)

I’ll start with Department and Category in the view, although ultimately, I don’t want Category in the view.  But having it there initially will help me see how things work at that level of detail:

First, I need a Level of Detail calculation that gives me the maximum Sales for all Categories in a given Department:

The hardest part of thinking through nested LODs is that I started with a view that is at a different level of detail than what I ultimately want.  Here, I already have Category as part of the viewLOD (view Level of Detail).  I’ve done that so I can see the results unfold before me, but I have to keep in mind that I’m going to remove it later, so I start with an INCLUDE to get the Sum of Sales for each Category.

Then I want to back up to the Department level to determine the MAX of all the Sums of Sales for each Category within a given Department.  So I EXCLUDE Category and find the MAX.

Now, I have the Sales of the highest selling Category within each Department and it’s easy to see which Category has the highest sales for the Department (it’s the one where the SUM(Sales) matches the MAX Sales):

So, I’ll write one more calculation to determine which Category has a Sum of Sales that matches the Max.  Here it is:

The trick here is to use INCLUDE to make sure I’m doing my comparison at the Category level (just like I did visually in the view above).  Since I want this to work even when I remove Category from the view, I’ll INCLUDE it here.

And now, I can remove Category from the view and only see the Max Category for each Department:

Sales here are the total sales for each Department, but I also have the Category with the most sales and the actual sales for that Category.

Extending the example

The best part is, I can add other dimensions and even swap other dimensions with Department.  For example, here is the highest selling Category for each State:

And I can even re-arrange the view to get other types of visualizations, such as this map:

And I could use this same approach to answer other questions (such as, which category has the lowest profit in the east?)

 

But let’s take it one more step…

What if I wanted to know how many states for each Max Cateogry?  In how many states were Office Machines the highest selling Category?  I could count them visually in the map or in view like this:

But, even better I can let Tableau do the counting and then visualize the results!

If I allow the Max Category to partition table calculations (simply right click the field on Rows and uncheck “Ignore in Table Calculations”).

This is the best part of the LOD calcs – they are aggregates (or sometimes even row-level) results that can be used to partition table calculations.  That gives us another layer to solve very difficult problems without having to layer complex table calc on complex table calc.

 

Then, the Size() function (computed along State – right click the field in the view and select Computer Using > State) will tell me how many states per Max Category:

State has to be in the vizLOD for the Size() function to return the right results.  But I don’t need to see the same result repeated state after state, so I will use the First() function (also computed along State) to keep only the first result of Size().

 

Moving, First() to Filters, State to Detail (it has to be in the view, but I don’t need to see it) and Size() to Columns allows me to have a view like this:

Wow!  Tables were the highest selling category in 11 states!  I should double check profit…

 

Everything up to the Extended Example could have been done fairly easily with table calcs.  However, using the LOD calcs allowed us to further partition table calcs that allows our analysis to go even deeper without having to get into incredibly complex table cacls.

 

 

I knew I loved LOD calcs when I first saw them.  But the more I work with them, the more I see the possibilities…