20 Uses for Tableau Level of Detail Calculations (LODs) (2024)

Tableau Level-of-Detail(LOD) calculations are incredibly powerful. In my opinion, every Tableau usershould know the basics of how (and when) to use them. Aside from the mostcommon use cases, such as eliminating the impact of duplicate records, they canbe leveraged in so many other scenarios. In this blog, I’ll share 20 differentuse cases I’ve come across.

This blog is goingto be focusing on use cases, so I won’t be explaining level-of-detailcalculations or how they work. I’m assuming some upfront familiarity with the concept.However, if you have not worked with LODs or would like a refresher, then hereare a few worthwhile articles:

Before gettingstarted, a few quick notes. First of all, I’ve published a workbook on Tableau Public which includes all of the examples shown below. I’ll also be referencing additionalexamples from the Tableau Community Forums. All of my examples will be using FIXED LODs, which tend to be the mostheavily used, but the concepts are generally applicable to both INCLUDE andEXCLUDE LODs as well. Depending on your data, the dimensions on your view, anda few other factors, INCLUDE or EXCLUDE may be a better option. Finally, it’s worthnoting that some of these examples could be accomplished using tablecalculations (in fact, Alexander Mou did exactly this in the his workbook, 20 Use Cases for Table Calculations). In some cases, these might provide a better solution, but I’m goingto focus on the use of LODs to address these problems.

1. Deal with Duplicate Records

Perhaps the most common use case forLODs is dealing with duplicate records. These duplicates are generally causedby one-to-many table relationships. For example, let’s say our Superstore Peopletable had 2 salespeople for each region. When you join Orders to People,each order will then be duplicated (in our case, each order will have tworecords instead of one). If you aggregate a measure, that result will then betwice the value you actually want.

With the new data modeling capabilities Tableau is developing, this issue should be much easier to deal with, buttoday the best solution is to leverage an LOD calculation. To deal with thesituation above, we could create the following LOD:

Sales LOD

// Eliminate the impact of duplicate salespeople.

{FIXED [Row ID]: MAX([Sales])}

In this case, we’re fixing on Row IDas we know it’s unique to each row in our Orders table, then we take theMAX of Sales. When we aggregate, we get this:

One question you may be asking yourselfright now is why doesn’t Tableau aggregate the value? For example, for Row ID1, the LOD returns 262. But, since there are two records, shouldn’t summing it(as I’ve done in the view above) cause it to be aggregated twice—once for eachrecord? The mystery of this lies in the coarseness of the LOD. Tableauhas a great discussion of this in the online help, so I won’t spend any moretime on it. However, I strongly recommend that you read the “Aggregation andLevel of Detail Expressions” section of How Level of Detail Expressions Work in Tableau.

2. Get a Single Aggregate

There are many scenarios where you maywant to get an overall min or max value. Let’s say you want to find the maximumsales amount for any order. We can create the following LOD to do that:

Max Customer Sales

// Highest sales value for any given record.

{FIXED : MAX([Sales])}

We want to get the overall max across allorders, so we’re not fixing on any specific dimension.

From here, we could use this value inany calculated field without any real concern for what dimensions are/are notincluded in the view.

3. Isolate a Specific Value

Sometimes you want to get an overallaggregate so you can do some comparisons, as detailed above. But sometimes youmight want to get some very specific value then make that available for otherpurposes. For example, let’s say you have the following data and wish to getthe last date upon which a customer purchased “Technology”

We can create the following LOD usingIF or IIF to isolate those values.

Last TechnologyOrder Date IF

// Get last date on which customers purchasedtechnology.

{FIXED [Customer Name]: MAX(

IF [Category]="Technology" THEN

[Order Date]

END

)}

Or, written using an IIF:

Last TechnologyOrder Date IF

// Get last date on which customers purchasedtechnology.

{FIXED [Customer Name]: MAX(IIF( [Category]="Technology", [Order Date], NULL))}

It’s worth noting here that, as shownabove, LOD calculations can be used for more than measure values. Whilemeasures tend to be the most common usage, we can use them to return dimensionvalues as well.

4. Synchronize Chart Axes

Let’s say you have four charts on yourdashboard—one showing sales for the West region and one showing sales for theEast.

This looks pretty good, but there’s oneproblem—the axes are not synchronized. In some situations, this might be okay,but in many situations, it can lead to misinterpretation by your users. In theabove case, I believe it is absolutely best to synchronize the axes so that youcan perform a fair comparison between the two. You could fix the axes to aspecific maximum, but that could fall apart eventually as the sales amountmight eventually exceed the maximum you’ve set.

This is where we can leverage one of myfavorite tricks—using a reference line to create an adjusting fixed axis. We’llstart out by creating an LOD to get the sum of sales, by month for each region.The above shows monthly sales, so we’ll want to fix on the month.

Region Month Sales

// Sales by region and month.

{FIXED [Region], DATETRUNC('month', [Order Date]): SUM([Sales])}

Note: It’s critical that you don’tsimply fix on the month (i.e. MONTH([Order Date]) as that will give you adiscrete value from 1 to 12. You need to ensure that you include the year,either by fixing on both year and month or by using DATETRUNC.

Then we can find the max of these forthe given regions:

Max RegionalMonthly Sales

// Maximum regionalmonthly sales

{FIXED : MAX([Region Month Sales])}

We now have a single calculated field thatgives us the maximum monthly sales value across all regions. Now we drop MaxRegional Monthly Sales on the detail card and create a reference line thatlooks like this.

Note: Be sure to turn off labels,tooltips, and the line so it’s invisible.

Once you’ve added the reference line toall sheets, the reference line will keep all the charts’ axes synchronized. Andthis is much better than fixing the axes because the calculated field will updateas the data changes.

5. Find Min/Max in a Time Series

There are times when you may want tofind the min or max value in a time series. For example, perhaps you have thefollowing chart and would like to highlight the min and max values.

We can create the following LODs:

Min Value

// Minimum total sales for a given month

{FIXED : MIN({FIXEDDATETRUNC('month', [Order Date]) : SUM([Sales])})}

Max Value

// Maximum total sales for a given month

{FIXED : MAX({FIXEDDATETRUNC('month', [Order Date]) : SUM([Sales])})}

We could use these to do any number ofthings, such as draw reference lines or bands.Or, with another calculated field, wecould plot points on the highest and lowest values.

Min or Max Sales

// Find a point for the min and max sales.

IF SUM([Sales]) = MIN([Min Value]) OR SUM([Sales]) = MAX([Max Value]) THEN

SUM([Sales])

END

Then, using a dual axis, we can plotthe points.

6. Get Related Data

LODs can be leveraged to find datarelated to a parameter or a dimension in your data. For example, perhaps youhave a list of customer sales and wish to compare those customers’ sales to aspecific customer in the data set. You’ll likely create a parameter for selectingthe customer (we’ll call it Selected Customer). We can then use an LODto get that customer’s sales:

Selected CustomerSales

// Sales for the customer selected in theparameter.

{FIXED : SUM(

IF [Customer Name]=[Selected Customer] THEN

[Sales]

END

)}

Note: This is really just a variant ofexample # 3.

You could then use that value to find avariance between each customer and the selected customer.

7. Turn Row Values into Measures

Tableau generally works best when your data is “tall and skinny” so whenever I can put my data in that format, I tryto. However, sometimes math is just easier when it can be performed on the samerow. For example, let’s look at Superstore data where the Sales and Profitmeasures are pivoted such that we have two columns, Measure (“Sales” or “Profit”) and Value.

In some cases, this may very well be the best structure for the visualizationyou are trying to create. However, some calculations can be really trickybecause the values are on different rows. For example, how do you calculateProfit Ratio (Profit divided by Sales)? In this case, a wider data structurewould be better because we could perform row-level calculations. So why nothave it both ways! We can use LODs to create measures for our Sales and Profitvalues:

Sales

// Isolate sales.

{FIXED [Region]: SUM(

IF [Measure]= "Sales" THEN

[Value]

END

)}

Profit

// Isolate profit.

{FIXED [Region]: SUM(

IF [Measure]= "Profit" THEN

[Value]

END

)}

Now that we have these measures on the same row, the math is relativelysimple as we can just divide Profit by Sales.

For another example of this technique, see the following forums post: How to Divide Calculated Fields

Note: You may have noticed this, but an LOD is not really required in this situation. This particular scenario could be done without it. And, whenever you can avoid an LOD, do it! Tableau has to issue additional queries to the database when using LODs, which means performance will be impacted. That said, there are definitely scenarios like this where an LOD would be required. The forums example linked above is one such scenario. Big thanks to Mina Ozgen for catching my mistake.

8. Get Point-in-Time Data

Sean Miller is my favorite customer inSuperstore—his data contains so many interesting stories and I always use himwhen teaching Tableau. So, let’s use him as an example here. What if we wishedto find the profit of Sean’s last order

We’d create an LOD like this:

Customer Last OrderSales

// Get the sales forthe customer's last order.

IF [Order Date] = {FIXED [CustomerName]: MAX([Order Date])} THEN

[Sales]

END

In some cases, you may want this valueto be available for other calculations. For example, let’s say you want tocompare each sales amount to the last order’s sales amount. To do this, we’dneed to wrap another LOD around this LOD:

Customer Last OrderSales (For Comparison)

// Get the sales forthe customer's last order and make available for comparisons to other salesorders.

{FIXED [Customer Name]: SUM(

IF [Order Date] = {FIXED [Customer Name]: MAX([Order Date])} THEN

[Sales]

END

)}

We could then create a simplecalculated field to get the difference as shown below.

9. Calculate the Mode

The mode of a set of numbers is the onethat appears most frequently. For example, if you have values 1, 2, 2, 2, 4, 5,5, 6, 7, then the mode is 2 because it appears more frequently than all theother numbers. A set of numbers can also have multiple modes. For example, ifthe above set had another 5, then this would also be a mode. However, mode isnot as commonly used as mean (average) and median, with which it’s often groupedin discussions of statistics. And there is no function in Tableau forcalculating the mode. Fortunately, we can use LODs for this.

For this example, let’s try todetermine the most common quantity of copiers purchased. I’ll start byfiltering my view by the “Copiers” sub-category. I’ll also add this filter tocontext so that it computes before my LOD (see the tips section at the end formore details on the Tableau Order of Operations). Then we can create thefollowing nested LOD calculation which will sum the number of records for eachquantity, then get the maximum of those sums.

Max Appearances

// Get the maxappearances of a given quantity.

{FIXED : MAX({FIXED [Quantity]: SUM([Number of Records])})}

Note: One interesting aspect of thisLOD is that we’re using Quantity, which is typically a measure, as a dimension.In this case, because we’re fixing on it, Tableau will use it as a dimensioneven though its default state is a measure.

We have the max number of occurrences,so we just need to compare the number of occurrences of each number to the max.Since there can be multiple modes, I’ll just create a calculated field thatreturns TRUE if the number is a mode.

Is Mode?

// Is this numberthe mode?

// Note: Could bemultiple modes.

IF {FIXED [Quantity]: SUM([Number of Records])} = [Max Appearances] THEN

TRUE

ELSE

FALSE

END

In this case, the most common quantityof copiers purchased is 2, with 21 purchases of that quantity.

10. Get Value from a Set

Set actions resulted in some of themost exciting innovation I’ve seen in Tableau since I started using it. But setactions (and sets in general) can be hard to get your head around. When usingset actions (particularly before parameter actions were available), I commonlyused them to interact with a chart and save the value I interacted with forlater use. For example, in my blog on Creating Zoomable Charts in Tableau (yeah, I know I keepmentioning this blog), I created a set on a date. When you hover over a givendate on an area chart, it triggers a set action and adds that date to the set.But, typically, you’ll need to inspect the set to determine which values areincluded. In the case above, I needed to get that date so I could then use itin some calculated fields. Unfortunately, getting a value from a set isn’tstraightforward. The set itself just results in an IN or OUT value. But, that’swhere we can leverage an LOD:

Value from Set

// Get thevalue selected in the set.

{FIXED:MAX(IIF([Your Set],[Field UponWhich the Set is Built], NULL))}

The IIF above will look at each row inyour data and if the value is in the set, it will return that value. By usingan LOD with MAX, we can isolate that one specific value from the set and use itin other calculated fields.

For more details on how this is used,please take a look at the zoomable charts blog referenced above. I’d alsohighly recommend that you check out the set actions work by Lindsey Poulter.

11. Use Aggregate as a Non-Aggregate

This is one of my all-time favorite tricks.You’ve spent a ton of time building a calculated field, complete withaggregations, to give you just the right output. But then you run into problemsbecause you try to mix that calculated field with non-aggregates or you try toadd totals to your view and find that the calculated field doesn’t sum as youexpect. In these situations, you can sometimes use an LOD to trick an aggregateinto acting like it’s not an aggregate. Because LODs always result in anon-aggregate, you can then use that field just like any other non-aggregatefield. But be careful with this as it can be dangerous to aggregate what isessentially already an aggregated value. Always make sure that what you’redoing results in a statistically valid result.

I’ve used this trick a few times on theforums and it’s pretty difficult to come up with a good example with Superstore,so I’m just going to point you to those examples.

12. Compare Current & Previous Year

One of the most common questions I seeon the forums is how to perform year over year comparisons. Depending on theperson’s goal and the structure of their data, this question can have numerousanswers. One of those, of course, is LODs.

Let’s say that we want to plot a linechart showing the 2019/2018 variance month. We can create a calculated fieldlike this:

Difference 2018 to2019

// Difference in2018 to 2019 sales by month.

{FIXED MONTH([Order Date]): SUM(IIF(YEAR([Order Date])=2019, [Sales], NULL))}

-

{FIXED MONTH([Order Date]): SUM(IIF(YEAR([Order Date])=2018, [Sales], NULL))}

Note: Generally, I try to avoid hard-codingyears into calculations like this as those will require updates when the nextyear rolls around. Whenever possible, I use a calculation to get the correct year.When that’s not possible, I’ll use a parameter. But for this example, I’m justhard coding in order to keep it simple.

We can then plot this on a line or areachart.

OK, I did it again. This is another one that does not require an LOD (Thanks Mina!!), but again, there are some situations where an LOD may be required to do such a calculation, so just be aware when you encounter it.

13. Find Occurrences of a Measure

We can use LODs to find occurrences ofa specific measure value. For instance, what if we wanted to count the numberof orders over $5,000 (for this example, I’ll use a parameter called OrderThreshold). We could build a view like this:

But what if we just want to get countof the orders? The above has a viz level of detail of Order ID. It then checksif SUM(Sales) is more than the threshold. If we want an overall count,we’ll need to remove Order IDfrom the view, which will cause the normal aggregation of sales to be performedat the new level of detail (the entire data set). So, we’ll need an LOD to forceour calculation to look at the sales for each order.

Orders AboveThreshold

// Return 1 fororders above the threshold.

IF {FIXED [Order ID]: SUM([Sales])} > [Order Threshold] THEN

1

ELSE

END

We can then aggregate this using SUM to get the total number of salesabove the threshold.

14. Compare a Ratio to the Max

Comparing some value or ratio to a maxis a fairly common requirement. You may need to do it, for example, when normalizingvalues for a parallel coordinates chart. An example I recently encountered on the forums required the user to color bar charts inthe context of a given category. For example, he had something like this,plotted for a few sub-categories.

Each bar was colored by the total salesamount. The problem was that some sub-categories, such as Appliances, have muchlower sales than others. So the color is never dark. Instead, he wanted to usecolor to show the magnitude of the sales within the context of each sub-category.In that case, the bar for Appliance sales in August, 2019 would be dark becauseit is the highest month for that particular sub-category. To do this, we need tofind the sales for each month then compare that to the sales for the highestmonth’s sales (for the given sub-category).

Month Sales % ofMax

// For eachsub-category, get the % of the max.

// Use this to coloreach sub-category on its own scale.

SUM([Sales])/ATTR({FIXED [Sub-Category]: MAX({FIXED [Sub-Category], DATETRUNC('month', [Order Date]): SUM([Sales])})})

We can then use this on the color card,setting the color range to go from 0 to 1 (0% to 100%).

15. Compare Subset to Superset

This example is similar to some of theother examples shown here, but slightly different. There are many times whenyou may wish to compare a subset of your data to a superset. For instance, let’scompare the average sale amount for each state to the national average. Becausewe’ll have a viz level of detail that includes State, we’ll need an LODto calculate the national average.

National AverageSales

// Average salesamount for the entire country.

{FIXED [Country/Region]: AVG([Sales])}

We can then do the math to compare eachstate’s average to the national average.

16. Get First Occurrence of an Event

Sometimes, you may need to get thefirst occurrence of some event. For example, perhaps you wish to find the firsttime that someone purchased more than 10 packs of “Staples” (seriously, whywould you need so many staples??)

We can clearly see that April 13, 2016was the first time. But how would we create a calculated field to get thisvalue? The following LOD should do it.

First Purchase Over10

// First purchase ofa quantity more than 10.

{FIXED [Product Name]: MIN(

IF [Quantity]>10 THEN

[Order Date]

END

)}

17. Find Record Meeting Some Criteria

Do you know how many Superstorecustomers have purchased only one product category? We could easily get a listof these customers by dragging Customer Name to our view then filteringon COUNTD(Segment).

But what if you just wanted a count ofthe customers? For this, we’ll need anLOD that counts the number of categories each customer purchased.

Bought One Category

// Did the customer buy only one category.

IF {FIXED [Customer Name]: COUNTD([Category])}=1 THEN

1

ELSE

END

We can then simply sum this measure toget our total.

Here are a couple of additional examplesof this technique from the forums:

18. Count Items Selected in a Filter

Have you ever wanted to count thenumber of items selected in a filter? You may want to do this to simply showthe user how many items they’ve selected or you may wish to take some other actionbased on this number. In any case, it’s LODs to the rescue!

Let’s say we have a bar chart showingsales by Sub-Category and we have a filter on Sub-Category.

We can create the following to countthe number of sub-categories selected.

Filter SelectionCount

// Count the number of values selected in thefilter

{FIXED : COUNTD([Sub-Category])}

I’ll then insert that into the titletext.

For this to work properly, you willneed to make sure that the filter is added to context. You also need to becareful when using this method as other filters can have an impact on theaccuracy of your count.

19. Rank Numbers (Kind of)

Wouldn’t it be great if you could useLODs for ranking and avoid those nasty table calcs! Well you can…kind of. Thismethod is really only applicable for very small sets of data and still has alot of flaws. But, in some very specific scenarios, it may come in handy. Let’ssay we have sales by Segment and wish to rank the values.

We can rank these using the followingcalculated field:

Rank

// Rank the sales by Segment.

IF SUM([Sales]) = ATTR({FIXED : MAX({FIXED [Segment]: SUM([Sales])})}) THEN

//Sales matches the maximum sales for a segment

1

ELSEIF SUM([Sales]) = ATTR({FIXED : MIN({FIXED [Segment]: SUM([Sales])})}) THEN

//Sales matches the minimum sales for a segment

3

ELSE

2

END

You can already see the limitations of this approach. If you had more than3 segments, then the comparisons would get increasingly complex as you’d haveto not only compare to the highest value, but the second highest, etc. Thismethod also does not account for ties. But, it could be useful in some veryspecific scenarios. For an example of one such scenario, see Text Showing Greater/Less Than Comparison

20. Using Filters, Show an “Other”Value

This is a fun one so I’ve saved it forlast. Let’s say we have a pie chart like this:

The pie chart shows sub-categories, butwe obviously do not want to show every sub-category at once (that would be apoor use of a pie chart). But, it’s also not a great use of a pie chart to showjust three of the sub-categories as a pie chart’s strength is its ability toshow part-to-whole relationships. So, we want our pie chart to show 2 or 3sub-categories separately, then show all other categories in an “Other” slice.But we want it to be flexible so that the user can choose those 2-3sub-categories from the filter.

We’ll need to start by unioning thedata to itself. We’ll then use one table for the 2-3 sub-categories and thesecond for “Other”:

Sub-Category New

// Sub-Category adjusted to include “Other”

IF [Table Name]= "Orders" THEN

[Sub-Category]

ELSE

"Other"

END

Then we use an LOD to get the overallsales amount.

Total Amount

// Get the total overall amount.

// Dividing by 2 to account for dataduplication from the union.

{FIXED : SUM([Sales])}/2

Then we create an adjusted salesmeasure:

Sales Adjusted

// Sales depending on if it is a regular sub-categoryor "Other"

IF ATTR([Sub-Category New]) = "Other" THEN

MAX([Total Amount]) - SUM([Sales])

ELSE

SUM([Sales])

END

Now we’ll build our pie chart. We placeSub-Category New on the color card and Sales Adjusted on the anglecard. Then we create a filter on Sub-Category. You’ll now have a piechart that lumps all unselected items into “Other”

LOD Tips

Before wrapping upthis blog, I wanted to share a few general tips for using LODs. For starters, Ioften use the following set of questions to determine if an LOD might benecessary:

1. Do you have tables with one-to-many relationships, which causes recordduplication?

2. Do you need an aggregate outside of the viz level of detail(could require a subset or superset of the data)?

3. Do you need an aggregate to act as astatic value for which to perform comparisons?

4. Do you wish to isolate some value or set of values from records outsideof the viz level of detail?

5. Do you need to aggregate something outside of the filters that are applied?

If your answer to any of thesequestions is yes, then an LOD may be required.

There are a few other things that arehelpful when using LODs. Perhaps the most important is Tableau’s Order ofOperations. See the visual below from the online help.

One of the most common problems peopleencounter with LODs is that their LOD is not respecting their filters. This istypically because the are using dimension filters (blue pills). But,as shown above, FIXED LOD calculations compute before dimension filters.This means that that a FIXED LOD’s return value is not impacted by changes tothe values selected in the filter. In some cases, this is exactly what you want(see question # 5 above), but in others, you want your filter to compute beforethe FIXED LOD. In that scenario, you need to make that filter a contextfilter (grey pill). As shown above, these compute before FIXED LODs. (Note:To add a filter to context, simply right-click the filter and choose “Add toContext.”) If you have not read the online help about the Order of Operations, stop what you’re doing and read it now as understanding theOOO is critical to understanding how Tableau works. You can also check out my blog on the Order of Operations.

One final tip I’ll provide is somethingI generally apply to any calculation that has a bit of complexity, including tablecalculations, LODs, etc. I personally always start by building a table beforejumping into building a chart. This may be fairly obvious already, consideringthe fact that most of the examples in this blog used tables. I just feel thatdata organized in rows and columns makes it much easier to validate andtroubleshoot the calculations. So my recommendation is to start with a table,ensure the calculations are correct (and, in the case of table calcs, ensure that you have them set upto compute properly) then apply them to your chart.

Wrap-Up

That was fun! As you might be able totell, I absolutely love LODs—they are so powerful and allow you to do somereally tricky things with Tableau. I hope you’ve enjoyed my examples and findit to be a useful reference in the future. Thanks for reading!

Ken Flerlage, February 19, 2020