---
title: Lecture Agenda
description:
duration: 5400
card_type: cue_card
---
## Tableau Lecture 5: Level of Detail calculation
#### Agenda
* Using Parameters to make visualizations more interactive
* Data Aggregation and Granularity
* Level of Detail calculation
* FIXED LoD
* INCLUDE LoD
* EXCLUDE LoD
Dataset: [sample superstore](https://docs.google.com/spreadsheets/d/1qduCkeB6NRyvK3qUhny3XTdVX2sq2frT/edit?usp=share_link&ouid=100659516601446935794&rtpof=true&sd=true)
---
title: Using parameters to make visualizations more interactive
description:
duration: 5400
card_type: cue_card
---
### Using parameters to make visualizations more interactive
* Parameters are useful when you want to add interactivity and flexibility to a report, or to experiment with what-if scenarios.
* Suppose you are unsure which fields to include in your view or which layout would work best for your viewers.
* You can incorporate parameters into your view to let viewers choose how they want to look at the data.
#### Business problem 1:
Allow users to select the field against which they want to compare the sales value.
**Steps:**
1. Create parameters where we display the fields that they can have on the Y-axis.
* Open the dropdown menu at the top of left pane, click on Create Parameter...
* Enter parameter name as "choose dimension"
* Select data type as String, Allowable values as List.
* Enter customer, product, and state in the list of values section. Click OK.
<img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/450/original/param_1.png?1693559927 height=400 width=700>
2. Creating a calculated field "chosen dimension", that will help us select the correct dimension based on the parameter value selected.
Formula:
CASE [choose dimension]
WHEN 'customer'
THEN [Customer Name]
WHEN 'product'
THEN [Product Name]
WHEN 'state'
THEN [State]
END
**Note:** The strings in the above formula have to match the string that we had entered when creating the parameter, otherwise it won't work.
<img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/452/original/param_2.png?1693560274 height=400 width=700>
3. Solving the business problem:
* Drag "chosen dimension" to Rows
* Drag Sales to Columns
* Right click "choose dimension" parameter at the bottom of left pane and click Show Parameter.
* Change sheet name to "Sales by <Parameters.choose dimension>" by using Insert Parameter option.
* Dimension can now be chosen from the right pane.
<img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/456/original/param_3.png?1693561049 height=400 width=700>
---
title: Data Granularity
description:
duration: 5400
card_type: cue_card
---
### What is Data Granularity?
* Data granularity is a measure of the level of detail in a data structure.
* Granularity in simple terms is how detailed the data is.
* The more detailed, the higher the granularity. (Hence the more rows of data you will have)
* The less detailed, the lower the granularity. (Vice versa, the less number of rows of data you will have)
**Example -**
* In time-series data, for example, the granularity of measurement might be based on intervals of years, months, weeks, days, or hours.
* The name field could represent the full name or have separate entries for first name, middle name, and last name.
<img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/457/original/granular.png?1693561189 height=200 width=300>
\
YouTube video for better understanding: [link](https://youtu.be/BZesMeOZCEc)
### Data Granularity vs Aggregation
* Granularity and aggregation work opposite of each other.
* In Tableau, when you bring dimensions and measure to the view, the measures are aggregated by whatever dimensions are on the view.
* As you remove and add more dimensions you are decreasing and increasing the granularity. The more dimensions/details on the view, the more granular it is.
<img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/459/original/gra_vs_agg.png?1693561357 height=200 width=400>
\
In this example, we have 3 dimensions out on the view so your viz level of detail is that line shown in the photo above.
Every measure on your viz will be aggregated at that level which means it will be aggregated by the combination of those 3 dimensions.
### Why is Data Granularity Important?
The level of data granularity determines what analysis can be performed on the data, and whether results from that analysis lead to appropriate conclusions.
The more granularity, the more information is available for analysis, but at the cost of increased storage, memory, and required computing resources.
Some analysis may require information to be analyzed at a higher level, which would require aggregating the underlying detail into the higher level of granularity this is where Level of details works magic.
---
title: Quiz-1
description:
duration: 30
card_type: quiz_card
---
# Question
Which of these features gives the most granular result?
# Choices
- [ ] Country
- [ ] State
- [ ] City
- [x] Customer ID
---
title: Quiz-2
description:
duration: 30
card_type: quiz_card
---
# Question
Which of these features gives the most aggregated result?
# Choices
- [x] Category
- [ ] Sub-Category
- [ ] Product ID
---
title: Fixed Level of Detail
description:
duration: 5400
card_type: cue_card
---
### Fixed Level of Detail
It computes a value using the specified dimension without reference to the dimension in the view.
The FIXED LoD can specify the granularity of the calculation independently of what is in the view.
This is what the syntax looks like:
<img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/476/original/formula.png?1693563031 height=25 width=400>
#### Business Problem 2:
Find the percentage sales contribution of each sub-category to the total sales of each product category.
**Steps:**
1. Getting the sum of sales for each product sub-category.
* Drag Sub-Category to Rows
* Add Sales to Text
<img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/482/original/lod_1.png?1693563408 height=400 width=700>
2. Creating Fixed LoD calculation
Calculation formula - `{FIXED[Category]:SUM([Sales])}`
<img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/484/original/lod_2.png?1693563643 height=400 width=700>
3. Solving the business problem:
Calculation formula - `SUM([Sales])/SUM([fixed sum of sales])`
* Create a calculated field "Percentage of Sales" using the above formula.
* Add **Percentage of Sales** to Label.
**Percentage of Sales** to Percentage, from the Measure Values card.
* Drag Category to Rows, keeping it to left of Sub-Category.
<img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/485/original/lod_3.png?1693563956 height=400 width=700>
---
title: Quiz-3
description:
duration: 45
card_type: quiz_card
---
# Question
If we want to find total profit for each region, do we need to have the feature 'Region' in the view?
# Choices
- [ ] Yes
- [x] No
---
title: Include Level of Detail
description:
duration: 5400
card_type: cue_card
---
### Include Level of Detail
It computes value using the specified dimension in addition to whatever dimension are in the view.
You can translate INCLUDE LoD as ‘For every dimension in the view and every listed dimension, calculate the aggregate expression’.
This is what the syntax looks like:
<img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/490/original/lod_inc.png?1693564354 height=50 width=200>
#### Business problem 3:
Find the difference between the total sales for each region and average sales per customer for each region.
**Steps:**
1. Creating an INCLUDE LoD calculation by customer name.
Calculation formula - `{INCLUDE[Customer Name]:SUM([Sales])}`
<img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/492/original/include_lod.png?1693564695 height=400 width=700>
2. Plotting the average sales per customer by region.
* Drag Region to Columns
* Drag Sales per customer to Rows
* Change aggregation of Sales per customer from SUM to AVERAGE in Rows
* Add Sales per customer to Text
* Change aggregation of Sales per customer from SUM to AVERAGE in Marks card.
<img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/496/original/in_lod_2.png?1693565114 height=400 width=700>
3. Solving the business problem:
* Drag Sales to Rows
* Click on Sales in Marks card
* Remove AVG(sales per customer) labels
* Add Sales to Label
<img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/498/original/inc_lod_3.png?1693565549 height=400 width=700>
---
title: Quiz-4
description:
duration: 60
card_type: quiz_card
---
# Question
Say we define 'sales per customer' as sum(sales) for each customer.
Is there any difference between 'Average sales per customer in Alabama' and 'Average sales in Alabama'?
# Choices
- [x] Yes
- [ ] No
---
title: Exclude Level of Detail
description:
duration: 5400
card_type: cue_card
---
### Exclude Level of Detail
EXCLUDE LoD is pretty much the opposite of INCLUDE. Instead of adding more dimensions, you’re getting rid of them ie we declare dimension to omit from the view level of detail.
You can translate EXCLUDE LoD as ‘For every dimension in the view EXCEPT the listed dimension(s), calculate the aggregate expression’:
This is what syntax looks like:
<img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/499/original/exclude.png?1693565843 height=50 width=250>
#### Business problem 4:
Find the difference between sales for each region by month and total sales for each month.
**Steps:**
1. Creating an EXCLUDE LoD calculation.
Calculation formula - `{EXCLUDE [Region]:SUM([Sales])}`
<img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/500/original/exclude_2.png?1693566125 height=400 width=700>
2. Plotting the sales by month for each region.
* Drag Order Date to Rows
* From Order Date dropdown in Rows, choose Month.
* Drag Region to Columns
* Drag Sales to Columns
* Change Marks from Automatic to Bar
* Add Sales to Label
<img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/502/original/exc_3.png?1693566481 height=400 width=700>
3. Using EXCLUDE LoD calculation in Color to get total sales done for each month.
* Add total monthly sales to Color.
<img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/511/original/exc_4.png?1693567224 height=400 width=700>
\
<span style="color: red;">**Important:**</span> Please inform the learners that they'll get the reference notes for today's lecture as a post-lecture content on their dashboard.
---
title: Quiz-5
description:
duration: 45
card_type: quiz_card
---
# Question
If we have state and city in the view, but we want sales aggregated over state only. What can the formula of the calculated field be?
# Choices
- [ ] {EXCLUDE [State]: SUM([Sales])}
- [x] {EXCLUDE [City]: SUM([Sales])}
- [ ] {INCLUDE [State]: SUM([Sales])}]
- [ ] {INCLUDE [City]: SUM([Sales])}]
---
title: Impact of Context Filter on LODs (Optional)
description:
duration: 5400
card_type: cue_card
---
### Impact of Context Filter on LoDs (Optional)
FIXED LoD calculations are applied before dimension filters. So, unless you promote the fields on your Filter shelf to Improve View Performance with Context Filters, they will be ignored.
Demonstration Video : [link](https://www.youtube.com/watch?v=aXuNVSnXwPk)
<img src= https://d2beiqkhq929f0.cloudfront.net/public_assets/assets/000/045/516/original/inpact.png?1693567765 height=250 width=400>
Sign in with Wallet
Connect another wallet