**LoD Type 1: FIXED**

**FIXED is arguably the simplest LoD to use in Tableau. It calculates the same value, the same way regardless of the viz you put it in. Let's see a simple example. We start with a chart that shows Sales by Category and Sub-category.**

Sales by Category and Sub-category |

{ (LoD Type) [Dimension1], [Dimension2], etc. : Metric }

Overall Total Sales |

Sales and Overall Total Sales by Category and Sub-category |

Sales and Overall Total Sales by Category and Sub-Category (with MAX()) |

**LoD Type 2: INCLUDE**

INCLUDE is a slightly more advanced type of LoD. An Include LoD looks at all of the dimensions in your chart, and adds another dimension to the list. For instance, let's look at Sales by Category.

Sales by Category |

Now, what if we wanted to know the Sales for the largest Sub-category within each Category. We can simply INCLUDE Sub-category to solve this.

Sales of Largest Sub-category |

This calculation will take the Dimensions in the chart (Category) and combine with the included Dimensions (Sub-category) to create a table of SUM( [Sales] ) by Category and Sub-category.

Sales by Category and Sub-category (again) |

Sales of Largest Sub-category by Category |

A quick comparison to the LoD table confirms that our calculations are correct.

Sales by Category and Sub-category (with Largest Sub-categories highlighted) |

{FIXED [Category], [Sub-category]: SUM( [Sales] )}

This is correct. In fact, most of the simple calculations can be done using a number of different calculations. The real differences come when the calculations get more advanced. But that's for a later post.

**LoD Type 3: EXCLUDE**

EXCLUDE works the exact opposite of INCLUDE. It removes a dimension that currently exists in your viz. For instance, let's start with a chart of Sales by Category and Sub-category.

Sales by Category and Sub-category (for the 3rd time) |

Total Category Sales |

Sales and Total Category Sales by Category and Sub-category |

It should be noted that when you are using INCLUDE and EXCLUDE, the value of the LoD may change if you change your viz. For instance, let's take Category out of the above chart.

Sales and Total Category Sales by Sub-category (or not) |

Using EXCLUDE to remove Sub-category now leaves us with no dimensions, which causes the Total Category Sales to reflect Overall Total Sales instead. For this reason, it's generally a good idea to accurately name your calculations. For instance, "Total Category Sales" should be "Sales Excluding Sub-category".

The final key difference between these 3 LoD Types is how they interact with filters. FIXED LoDs are designed to work independently of the chart. Therefore, their values are not affected by traditional filters. They see what the chart sees (also known as the context). However, INCLUDE and EXCLUDE LoD's are designed to work in the confines of a single viz. Therefore, they are affected by traditional filters. This leaves us with the following order of calculation.

1) Data Source Filters

2) Context Filters

3) FIXED LoDs

4) Traditional Filters

5) INCLUDE and EXCLUDE LoDs

We still have a tremendous amount of room to explore the great new feature of Level of Detail Calculations. Thanks for reading. We hope you found this informative.

The workbook for this post can be found here.

P.S.

A couple users on the Tableau forums pointed me towards this graphic which I think tells the story perfectly.

Brad Llewellyn

Director, Consumer Sciences

Consumer Orbit

llewellyn.wb@gmail.com

Director, Consumer Sciences

Consumer Orbit

llewellyn.wb@gmail.com

http://www.linkedin.com/in/bradllewellyn

Dear Brad,

ReplyDeleteThanks for the good post. What if I want to filter before the fixed LOD?

For example { FIXED [Job id] : MIN([Date])}

I am looking at each job id and getting the min date. What if I want to filter on a third variable say Status. I could do { FIXED [Job id], [Status] : MIN([Date])}, but that would run on all Status. I would rather run only if Status = say "hired".

Interested on what you think.

Hi Brad, I have a couple problem that i was hoping you can help me out with. I have a LOD calculation similar to the one that you have above. the problem is that I want to use quick filters to drive the LOD. for instance, i want to get the { fixed sub-cate: sum(sales)} broken down by state, So i want to be able to add state and county to the filter, I want to be able to select state from the quick filter and be able to get total sales for all appliances. Also i want to be able to select county and i want to be able to get the totals as well for the counties that have appliance

ReplyDelete