Understanding the terms Aggregation, Granularity and Level Of Detail and how to relate them when working in Tableau?
Probably one of the most important and underrated topics when working on any BI tool including Tableau is understanding the terms Aggregation, Granularity and Level Of Detail. These terms are quite confusing for anyone just starting their journey in Tableau and Data Analytics.
NOTE: This article has examples created in Tableau but these concepts are applicable to any other BI Tool.
Understanding Aggregation
Aggregation is a mathematical operation by means of which we combine multiple values and return a single value. These can be operations like sum, average, count, or minimum etc.
In Tableau, we can aggregate measures or dimensions, though it is more common to aggregate measures.
Whenever a measure is added to a view, an aggregation is applied to that measure by default. The type of aggregation applied varies depending on the context of the view.
When a measure is dragged to the view, Tableau automatically aggregates its values. The default aggregation is Sum however there are other aggregations like average, median etc.
The current aggregation appears as part of the measure’s name in the view. For example, Quantity becomes SUM(Quantity).
Every measure has a default aggregation which is set by Tableau when we connect to a data source. We can view or change the default aggregation for a measure.
Changing the Aggregation of a Measure in the View
The aggregation for a measure in the current view can be changed from the Measure pill
Right click (control-click on Mac) the measure or click on drop-down in measure pill > Measure(Sum) > Average (for example)
NOTE: This is for a field in the Rows or Columns shelf.
NOTE: This aggregation change will be applicable only for that sheet or viz and NOT to the whole workbook (all sheets)
Aggregating Dimensions
A dimension can be aggregated in the view as Minimum, Maximum, Count, or Count (Distinct)
When we aggregate a dimension, a new temporary measure column is created, so the dimension takes on the characteristics of a measure.
Right click (control-click on Mac) the dimension or click on drop-down in dimension pill > Measure > Count (for example)
Set the Default Aggregation for a Measure
The default aggregation can be set for any measure that is not a calculated field that itself contains an aggregation, such as AVG([Sales])
A default aggregation is a preferred calculation for summarizing a continuous or discrete field
The default aggregation is automatically used when you drag a measure to a view
Right-click (control-click on Mac) a measure in the Data pane and select Default Properties > Aggregation, and then select one of the aggregation options e.g.: Average
NOTE: This aggregation change will be applicable for the whole workbook (all sheets)
How to Disaggregate Data
Whenever a measure is added to a view, an aggregation is applied to that measure by default
This default is controlled by the Aggregate Measures setting in the Analysis menu
The default view will be an aggregated one
For the below default view the number of marks in the status bar is 4
If there is a requirement to see all the marks in the view at the most detailed level of granularity, we can disaggregate the view.
Disaggregating the data means that Tableau will display a separate mark for every data value in every row of the data source
To disaggregate all measures in the view
Clear the Analysis > Aggregate Measures option
If it is already selected, click Aggregate Measures once to deselect it
This view will now be a disaggregated one.
The number of marks in the status bar is now 9994 (i.e., total number of rows in the data source).
How to Disaggregate Data in Scatter Plot
Assume that we have a Scatter Plot of Sales Vs Profit
By default, we will have a one-mark scatter plot since both Sales and Profit will be aggregated as Sums
We can display more marks by disaggregating the data.
Clear the Analysis > Aggregate Measures option
If it is already selected, click Aggregate Measures once to deselect it
Now you see a lot of marks — one for each row in the original data source i.e., 9994
How Aggregated Data changes when Dimensions are added
Assume that we have a Bar Chart of Sales Vs Category
The Sales Axis varies from 0K to 850K
There are 3 marks in this viz
Assume that we add a dimension Sub-Category in Rows shelf
The Sales Axis now varies from 0K to 340K
There are 17 marks in this viz i.e. More granular and less aggregated view
What is Data Granularity?
Data granularity is the level of detail in a data set / data source.
In time-series data, for example, the granularity of measurement might be based on intervals of years, months, weeks, days, or hours.
For transactional data, granularity might be at the purchase order level, or line item level, or detailed configuration level for customized parts.
Why is Data Granularity Important?
The level of data granularity will determine the type of analysis that can be performed on the data, and whether results from that analysis lead to appropriate conclusions for the related problem statement.
The more granularity, the more information is available for analysis, but at a cost of increased storage, memory, and other required computing resources. However some analysis may require information to be analyzed at a slightly higher level, which would require aggregating the underlying data into a higher level of granularity.
Aggregation and granularity are actually complementary concepts. When we move from a single value to an aggregated value i.e. combining multiple values it results in a higher aggregation but a lower granularity.
Various Types of Level Of Detail
Given below are the three primary types of Level Of Detail in Tableau:
Level Of Detail of the Data
Level Of Detail of the Viz
Level Of Detail Calculations
Level Of Detail of the Data
Level of detail of the data refers to the granularity of the data set or data source
This is the first thing that we need to identify right after connecting to the data set. This should be done before we start working on the visualizations
The granularity will provide the information about “What is one row or record in the data source?“
For example, if we consider the Sample Superstore data source it is an example of a transactional data
This means that each row or record is a transaction that happens in the Superstore
The granularity of this Sample Superstore is not at the Order Level as the Order ID is not unique
Refer to Order ID being repeated for Row ID 1 and 2
The granularity is at the Product Level i.e., Product ID since it is a transactional dataset
Level Of Detail of the Viz
When we make use of dimensions and measures in our visualization by default the measure will be aggregated at the Level Of the Viz
Consider that we have a Horizontal Bar Chart of Sales Vs Segment
Here the SUM([Sales]) will be aggregated at the level of the Viz in this case it is Segment.
Consider that we have a Scatter Plot of Sales Vs Profit with Product Name in Detail and Segment in Color.
In this case we are having two dimensions hence the Level of detail of the Viz will be at Product Name and Segment level
Hence each mark in the Scatter Plot will correspond to a specific Product Name and a specific Segment
In case we increase the number of dimensions the marks in the Viz will also increase thereby increasing the granularity of the Viz
Next if we have a Stacked Horizontal Bar Chart of Sales Vs Sub-Category with Region in Color
In this case all dimensions will define the granularity of the viz even the dimension in Color i.e., Region
The segment marked in green will represent the SUM([Sales]) for a combination of all dimensions in the Viz
Phone Sub-Category under Technology Category in the East Region has a Sales of 100,615
View/Viz Level of Detail (or) Granularity further explained
Whenever we drag any dimension into the view the level of detail or granularity of the view will change (based on the number of members of the dimension)
For example, if we drag Category into the Rows shelf, we get 3 marks as the Category dimension has 3 members viz. Furniture, Office Supplies and Technology.
This can be confirmed by checking the Status Bar at the bottom of the Tableau workspace
Next let us drag and drop the Sales measure to the Columns shelf
Now we see that each Category has the SUM(Sales) shown by the mark labels
Next let us drag and drop the Sub-Category dimension to the Rows shelf
Now the view is more granular or less aggregated
Hence, we can say that the level of the detail of the view is now changed from 3 to 17
Turn ON the Summary Marks card
The total sales of the Office Supplies category i.e., $ 719,047 is now broken into various Sub-Categories like Appliances, Art, Binders etc.
We can now increase the granularity of this view by adding Region to the Columns shelf
The level of the detail of the view is now changed from 17 to 68
We can also move the Region pill to Color of Marks card
Surprisingly the same level of granularity is maintained i.e., we get the same number of marks i.e., 68
We can compare one specific mark for both cases i.e., Region on the Columns shelf and Region on the Colors of Marks card
SUM(Sales) is $24,157 for the below mentioned combination of dimensions
Category: Furniture
Region: Central
Sub-Category: Bookcases
Parts of a view that affect the Viz Level Of Detail
Dimensions and Sets placed on the areas marked in blue will affect the Level Of Detail of the Viz
Dimensions and Sets placed on the areas marked in red will NOT affect the Level Of Detail of the Viz
Given below are the important points with respect to View/Viz Level of Detail or Granularity
Point#1: It is controlled by Rows , Columns shelf and Color Marks card
Point#2: It is primarily controlled by the Dimensions
NOTE: The other sections of the Marks card can also be used e.g.: Size, Label, Text, Detail, Shape, Path, Angle. But the change in the Viz is visually more noticeable if we make use of Color.
Even Pages Shelf and any fields directly dropped into the view will also result in change in the view level of detail.
NOTE: There will be no change in granularity if the dimension is moved to the Tooltip of Marks card and the Filters shelf
In some instances, or use cases we might need to perform calculations or analysis that are not at the View Level Of Detail
This is where we need to make of Level Of Detail Expressions (LOD)
Level Of Detail Calculations
When we drag measures into the Viz, we know that by default the measures will be aggregated at the Level Of detail of the Viz.
But there might be a requirement to carry out the calculations or perform analysis to a level that is not related or is independent to the viz.
In this case Level Of Detail Calculations or Expressions will be useful.
Assume we have a Sales Vs Category and Sub-Category.
We might have the Viz broken down up to the Sub-Category, but we may require the Sales only at the Category level
In this case FIXED LODs can be used
{ FIXED [Category] : SUM([Sales]) } will calculate the Sales at the Category level
How to relate the terms Aggregation, Granularity and Level Of Detail?
The below image describes how the terms Aggregation, Granularity and Level Of Detail are related to one another.
“More aggregated” data → “Less granular” data → “Higher level of detail”
“Less aggregated” data → “More granular” data → “Lower level of detail”
This means that Aggregation and Level Of Detail have a positive relationship (or are directly proportional) while Aggregation and Granularity have a negative relationship (or are inversely proportional).
I hope this article is useful for all Tableau users especially for anyone just starting their Tableau journey!!!
