Introduction to LOD Functions

If you’re like me you’ve spent hours putting together the perfect dashboard only for it to be ruined by a filter or dashboard action.  Or perhaps you have had a great idea for a plot but you weren’t able to create it using variable you wished.  Both of these issues and many more can be fixed simply by using LOD calculations.  Not only do LOD calculations allow you to have more dynamic visualizations but also open up the possibility of creating even more variables.  In this blog I will run through the three LOD functions and give examples of were you can use them.

The formula for all LOD functions are as follows:

 

{LOD [Dimension] : AGG[Measure]}

 

More than one dimension can be included in the LOD value they just need to be separated.  LODs can also be nested but that will be saved for another time.  All examples below will be using the Superstore data so you can copy these examples if you wish.

 

Include

The first function we will look at is also one of the most commonly used, Include. As the name suggests, this can be used to include a variable into the level of detail.  Starting with a chart showing the average sales for each Region.  The chart below shows the average of each row in each Region, not each order.

 

 

To get the average of each order we would need to include that into the level of detail.  Currently we are seeing the average value of each item in each order which is not very useful.  So to group it by Order ID we will add the following calculated field:

 

{INCLUDE [Order ID] : SUM(Sales)}

 

If we drag that field into the Columns bar we will get a new chart which shows different numbers as it is now grouped by order.  The value has come up and although Order ID is nowhere on the visualization or marks card it is being included in the calculation.  This calculated field will include Order ID in any aggregation or visualization up until the level of detail gets below Order ID.

 

 

Exclude

Exclude is the function that you will most likely use least. It is used when you want to have a variable in the visualization but not used it in the calculation/aggregation.  It is often used with Percentage of Total or Difference calculations.  For the example below I have excluded the Sub Category value when calculating the average.

 

{EXCLUDE [Sub-Category] : AVG(Sales)}

 

This will now allow us to compare each Sub-Category’s average sales to the Category’s average sales and calculate the difference.  Anytime the Sub-Category variable is included in a visualization or calculation it will be ignored so that the level of detail above it is used.

 

 

The chart above shows the process used to get each Sub-Category’s difference from their Category’s average.  On the left is each Sub Category’s average sales.  In the middle is the exclude LOD which shows just the Category average, even though it is not in the visualization or marks.  On the right is the left column minus the middle column, the difference between the Sub-Category average and it’s Category’s average.

 

Fixed

Finally, my favourite and possibly the LOD function you will be using the most.  Like INCLUDE, Fixed will make sure the dimension included in the formula will be included in any calculations where the field is used.  You can also fix the level of detail on more than one variable.  By fixing to a measure it will be the lowest level of detail for any visualization using that field.  It will also be included in any visualization where the calculated field is included.  For example, if I wanted to see the average sale for each Product in each State I would do the following:

 

{FIXED [State], [Product ID] : AVG(Sales)}

 

Then if I was to compare that to the average row value in each Region I would get the following:

 

 

The plot shows the average sales per row in orange and the average sales per Product and Region in purple.  We can see that in most cases the average product and region is higher than each row, suggesting that lower cost products get ordered more on average.  It would be easy to assume an average of each row is the correct thing to display but it is often not the case and FIXED can help us get around that.

 

Conclusion

There are many different uses for LOD functions and this blog shows just the beginning of what’s possible.  From here you can take these functions and create nested LOD calculations which take the process even further.  While being complicated, LODs open up so many more possibilities for users and are a must for developing Tableau skills.  Please feel free to comment some of your favourite uses or suggestions.

 

Thanks for checking out my blog!

 

 

 

 

 

 

 

Mikael Nuutinen
Author: Mikael Nuutinen