Level of Detail in tableau

Introduction :

The level of detail(LOD) determines the number of marks in your view. When you add a level of detail expression to the view it helps us to extract the measure we want. It allows us to compute values at the data source level and the visualization level. They used to run complex queries involving many dimensions at the data source level instead of bringing all the data. They are used to control the level of detail (granularity) of a calculation. Most fields and calculations are tied to the granularity of the view or of the data source. LOD expressions allow you to set the granularity of a calculation explicitly.

There are three types of Level of Detail(LOD) :

1. Fixed LOD

2. Include LOD

3. Exclude LOD

FIXED LOD :

FIXED level of detail expressions computes a value using the specified dimensions, without reference to the dimensions in the view. These expressions can have a finer level of detail than the view, a coarser level of detail, or the same level of detail. FIXED LOD expressions establish a specific dimension or dimensions for the rest of the calculation. For example, returning the maximum blood pressure for each patient instead of the overall maximum in the data set by fixing the maximum to Patient ID. For a better understanding let's take a segment and region-wise sales.

Let's take segment and region (dimension ) in the rows and sales (measure) in the text of marks.

Here we can observe the total sales of consumers in central, east, south, and west. But now we want segment-wise sales because the above example includes all four regions and the sales getting divided among the regions.

But we want to extract segment-wise like consumer sales, corporate sales, and home office sales.

Here if we remove the region we can get segment-wise sales detail as shown below but if there are many dimensions we can able to remove all those dimensions.

In this situation, we can use "Fixed LOD".It is created by using calculated fields. select the calculated field a pop-up window will appear there enter the formula as shown below

A newly created calculated field will appear as shown below

When double click the "fixed" it is automatically included in marks and fixed sales will be appeared as shown below

segment-wise sales detail taken calculated separately

The values of fixed sales and segment-wise sales are the same. This fixed LOD is used to measure particular dimension among others.

INCLUDE LOD :

INCLUDE level of detail expressions can be useful when we want to calculate at a fine level of detail in the database and then re-aggregate and show at a coarser level of detail in our view. These expressions ensure that a dimension that isn't necessarily present in the view is considered in the calculation. For example looking at the average blood pressure for each patient including their sex, displayed in a view that doesn't contain sex information. Let's take sub-category-wise sales.

Take sub-category(dimension) in rows and sales(measure) in the text of marks as shown below

Let's change SUM ( sales ) into AVERAGE ( sales ).

Let's include the total from analytics and the total will be viewed in the table.

As we notice the total of subcategories total won't be "230" To get the exact value click on the value and select the "average" option.

After selecting it our total average will change to "429".

Let's see this using "include LOD".

Include LOD calculated field :

After applying "include " to the marks we get the total of "7292" which is the sum of include lod.

Change the SUM(include) into AVERAGE(include) by clicking the drop-down button of sum ( include) in marks and select measure average.

After applying the changes, the final average value is the same.

EXCLUDE LOD :

Exclude LOD helps in the prevention of calculations from one or more dimensions. They are often used in "percent of the total " or "difference from the overall average".They cannot be used in row-level expressions, where there are no dimensions to omit. But can be used to modify either a view level calculation or anything in between (that is, you can use an EXCLUDE calculation to remove dimension from some other level of detail expression). For example looking at the average blood pressure for patients on a certain medication without considering individual patients, even if the view is by the patient. Let's take a category and segment-wise sales.

Category and segment(dimensions ) were given in rows and sales in the text of marks.

Let's exclude the category sales and extract the segment-wise sales. Creating exclude formula using calculated fields.

A formula for excluding LOD has been created and it will be visible under measure names. To get segmentwise sales let's include the excluded LOD formed in the calculated field into marks. It can be included by double-clicking on it.

This excludes contains segment sales excluding the category sales. Likewise, when we have two or more dimensions we can exclude the no of dimensions we want and extract the report which we want.

In all LOD we can include more than one dimension. There is no limit in giving dimensions in the calculated field. Let's have an example of excluding LOD including two dimensions to exclude.

Let's have category, segment, and region in rows and sales in marks as shown below.

Now let's exclude category and segment and extract region-wise sales. Now the calculated field will change into

After including it in marks the exclude will show the region-wise sales . Through this, we can able to exclude more than one dimension.

NOTE :

• A FIXED LOD expression will have the same value because it calculates only the dimension in the LOD expression.

• An INCLUDE LOD that includes the dimension given in the LOD expression and values differs depending on the expression given.

• An EXCLUDE LOD that excludes the dimension given in context and values will depend on the remaining dimension other than the excluded dimension.

Conclusion :

This blog gives a brief about the Level of Detail. We often use fixed LOD than other LOD. It runs complex queries involving many dimensions at the data source level instead of bringing all the data to the Tableau interface. Hope this blog is useful to understand LOD in tableau.