Think of your data set as a pyramid. The data you upload creates the base of the pyramid – the top is the sum of all the data. LOD expressions give you a way to create layers in your data and use those layers to categorize data into dynamic groups, sets, or ways to work around the limitations of table calculations –

## LOD Syntax

** **

LODs use a keyword – **Fixed, Include, or Exclude** – followed by a list of dimensions that set the level of the virtual layer you want to create in the data set – For example

**{Fixed [Segment], [Category]** **:** …

In words it says “make all the combinations of Segment (3 in Superstore data) and Category (3) ” and create a layer that consists of 9 total values – then save them so they can be used later

The colon : is the divider that ends the layer definition and starts the formula of the value to save –

{Fixed [Segment], [Category] : **sum(Sales)}**

The result is a new layer in the data at the Segment/Category level

** **

## Creating multiple layers

Adding dimensions before the colon will define another layer in the data. To create a new layer that includes Year level place the Year(order date) in the dimension list

{ FIXED [Segment],[Category],year([Order Date]): sum([Sales])}

Now in addition to layer at the Segment/Category level, there is a layer at the Segment/Category/Year level

## Filtering and the Order of Operation

LOD expressions can be filtered but the results will be affected if dimensions placed in the Context Filter

** **

First, use a simple filter on a Dimension in the LOD expression – ( Segment ). The results are filtered as you would expect – the Consumer Segment is filtered out of the data – Three of the 9 values of the Segment/Category are filtered out of the result

## Context filters affect

** **

Add a filter for a Dimension not in the LOD expression Region in the example – filter the Central region and the 9 values from the LOD are NOT changed –

Change the Region filter to **Context** – the results do change

What's going on?

Context filters are applied BEFORE the LOD calculation in the Order of Operation – if a dimension is placed in Context Tableau will apply the filter before the LOD is calculated –

In this case – the Central Region is filtered out before calculating the Segment/Category LOD

Dimension filters are applied AFTER the LOD is calculated – the results reflect the un-filtered Segment/Category LOD

## Use in other calculations

** **

LODs place a value that is aggregated in a new virtual layer in the data set, but they are NOT aggregates in themselves. **That’s powerful!**

## Replacing Table calculations

** **

A FAQ on the Forum is how to fix a table calculation – but they are built on the table that underlays each worksheet and can’t be carried from one sheet to another.

LOD’s can be used to "Fix" some table calculations – for example, Percent of total

The table calculation formula for percent of total used Total() and is then calculated down (in this example)

SUM([Sales]) / TOTAL(SUM([Sales]))

LOD’s can be used to replace the numerator and denominator

sum({ FIXED [Segment],[Category]: sum([Sales])})/sum({ FIXED [Category]: sum([Sales])})

The LOD base Percent of Total and now Fixed at the Segment/Category level

## Nested LOD and Table Calculation

LODs can be also be nested directly in Table Calculations – This example dynamically ranks Sub-Category based on annual sales

The LOD below will total Sales at the Sub-Category/Year level

{ FIXED [Sub-Category],Year([Order Date]):sum([Sales])}

Nesting the LOD in a Ranking function (Table Calculation) will sequence the categories descending each year

RANK_UNIQUE(sum([fixed annual category sales]),'desc')

#### LODs in conditional statements and grouping

** **

Need to separate records into groups based on the value of a single dimension?

Use a conditional statement ( if…then ) in the aggregation portion of the LOD (after the : )

This says “for each order and category combination – separate out the Furniture lines and group the remaining as “Other”

{ FIXED [Order ID],[Category] : min(if [Category]="Furniture" then [Category] else "Other" end ) }

The LOD will categorize each Order/Category combination into one of 2 groups – you can use the LOD like any other dimension in your viz

#### Include and Exclude LODs

** **

Where Fixed works above the dimension filters in the order of operations Include and Exclude work after the dimension have been applied

Using the same Segment/Category but adding the Include and Exclude version we can see the effect of the LOD

{ FIXED [Segment],[Category]: sum([Sales])}

{ EXCLUDE [Segment],[Category]: sum([Sales])}

{ INCLUDE [Segment],[Category]: sum([Sales])}

Include is applied after the dimension filter Year(order date) is applied and sums at the Segment/Category for each year

Exclude applies the totals after the Year(order date) is applied and sums across all the Segment/Categories at the year level

I hope this gives you a better idea of how LOD expressions give you the flexibility to add different levels in your data set. They are a powerful tool that can be used in conjunction with conditional statements, table calculations or nested in other LODs.

Have fun – the best way to learn more is to use them with some simple example

Jim