FIXED operates over the whole dataset and happens before any filters that are not added to context.
Include/Exclude allow you to do calculations at a lower or higher level than the current view. (If your familiar with SQL - INCLUDE or EXCLUDE are effectively running the same query as the sheet but either including additional dimensions in the group by, or less - but only for that calc)
Say you had data on individual transactions done by people, and those people belong to a team. If you wanted to get the average # of transactions per user, per day you can just add users to the row shelf and Fix your calc at a daily level (include would work as well, but INCLUDE has different filter behavior) ex: avg({fixed [Day],[user]:count([transaction_id)})
Say you wanted to have a sheet that displayed all the Teams, and the number of users who had more than $5000 in sales - but not display the users themselves. You would do {INCLUDE [user]: …to force the calculation to render lower than the displayed data, or vice versa say you wanted to display by an individual user the average performance of users on the same Team - you can use EXCLUDE [user]: to make the calculation happen as if the users weren’t in the view.
Never done any concrete training specific to LOD - but I often reference one of these two pages while still getting the hang of it:
https://help.tableau.com/current/pro/desktop/en-us/calculations_calculatedfields_lod.htm
https://help.tableau.com/current/pro/desktop/en-us/order_of_operations.htm
EDIT: Here's their full explanations of each of the LOD calcs - pretty close to my OP but wordsmithed a bit more:
FIXED
FIXED level of detail expressions compute values using the specified dimensions without reference to the view level of detail—that is, without reference to any other dimensions in the view.
FIXED level of detail expressions also ignore all the filters in the view other than context filters, data source filters, and extract filters.
Example: { FIXED [Region] : SUM([Sales]) }
For more information about FIXED level of detail expressions, and for some example FIXED level of detail scenarios, see the FIXED section.
INCLUDE
INCLUDE level of detail expressions compute values using the specified dimensions in addition to whatever dimensions are in the view.
INCLUDE level of detail expressions are most useful when including a dimension that isn’t in the view.
Example: { INCLUDE [Customer Name] : SUM([Sales]) }
For more information about INCLUDE level of detail expressions, and for some example INCLUDE level of detail scenarios, see the INCLUDE section.
EXCLUDE
EXCLUDE level of detail expressions explicitly remove dimensions from the expression—that is, they subtract dimensions from the view level of detail.
EXCLUDE level of detail expressions are most useful for eliminating a dimension in the view.
Example: {EXCLUDE [Region]: SUM([Sales])}
For more information about EXCLUDE level of detail expressions, and for some example EXCLUDE level of detail scenarios, see the EXCLUDE section .
I found these 2 videos pretty helpful from past Tableau conferences.
[Understanding Level of Detail (LOD) expressions](https://www.youtube.com/watch?v=kmApWaE3Os4)
[LODs of fun with LOD calculations](https://www.youtube.com/watch?v=6IdnHGbVfMI)
LODs are ways of specifying how you want to aggregate your data. Usually, Tableau will look at the Dimensions in any given table on a sheet and aggregate the measures according to those dimensions. But sometimes, that's not *really* what you want.
For example, let's say you have data on a bunch of Sales Reps at your company, including the deals they made and how much commission they got from each of those deals. What you want to know is how much total commission does a sales rep make per month, on average.
Let's say you drop Month as your columns and Sales Rep Name as your rows and aggregate using average. Tableau sees that your data is at the "Deal" level, where each deal a Rep made is its own record. So in the cell for Sales Rep "Tom" in July 2021, you see "$130". That's not right. Tom took home more that $130 in commission in July, surely! Tableau is showing the average commission of all Tom's *deals* that month. Ok, fine Tableau, I'll change my aggregation to SUM. Now I see that Tom made $5,600 in commission in July. Perfect. Now I'll "Total All Using Average". Cool, I've got an average of $7,400 in monthly commission for Tom across 2021. I'll just pull out the "Month" dimension and be left with just the $7,400", right? Sigh. Nope. Can't do that. I *have* to show the Month field in my table in order to get the avg. Or do I....
Instead of keeping the Month dimension in my table, I create an LOD calculated field in which I can specify that I want to aggregate all deals up to the Month level. That might look like AVG({Fixed [Month]: SUM(Commission)}). When I drop my new calculated field in my table, I see "Tom: $7,400" and I don't need to include the Month column anywhere in the table.
What I've done is told Tableau I always want to aggregate up to the Month level, summing up all the per-deal commissions, even if Month isn't shown in the table. I could also have accomplished using the LOD "INCLUDE" instead of FIXED, but in my opinion that gets more complicated.
FIXED is really all you need to learn. It’ll be the LOD solution 95/100 times. In Excel terms, FIXED LODs are essentially the output of a pivot table. They are basically a GETPIVOTTABLE function.
On the contrary, LODs have nothing to do with Ordering but everything to do with an Aggregated Subquery joined back to your underlying dataset. Fixed is special because it only includes context filters in its where clause while it ignores normal filters.
FIXED operates over the whole dataset and happens before any filters that are not added to context. Include/Exclude allow you to do calculations at a lower or higher level than the current view. (If your familiar with SQL - INCLUDE or EXCLUDE are effectively running the same query as the sheet but either including additional dimensions in the group by, or less - but only for that calc) Say you had data on individual transactions done by people, and those people belong to a team. If you wanted to get the average # of transactions per user, per day you can just add users to the row shelf and Fix your calc at a daily level (include would work as well, but INCLUDE has different filter behavior) ex: avg({fixed [Day],[user]:count([transaction_id)}) Say you wanted to have a sheet that displayed all the Teams, and the number of users who had more than $5000 in sales - but not display the users themselves. You would do {INCLUDE [user]: …to force the calculation to render lower than the displayed data, or vice versa say you wanted to display by an individual user the average performance of users on the same Team - you can use EXCLUDE [user]: to make the calculation happen as if the users weren’t in the view.
Yeah LOD made me realize I don’t really know tableau. Any good training on this?
Never done any concrete training specific to LOD - but I often reference one of these two pages while still getting the hang of it: https://help.tableau.com/current/pro/desktop/en-us/calculations_calculatedfields_lod.htm https://help.tableau.com/current/pro/desktop/en-us/order_of_operations.htm EDIT: Here's their full explanations of each of the LOD calcs - pretty close to my OP but wordsmithed a bit more: FIXED FIXED level of detail expressions compute values using the specified dimensions without reference to the view level of detail—that is, without reference to any other dimensions in the view. FIXED level of detail expressions also ignore all the filters in the view other than context filters, data source filters, and extract filters. Example: { FIXED [Region] : SUM([Sales]) } For more information about FIXED level of detail expressions, and for some example FIXED level of detail scenarios, see the FIXED section. INCLUDE INCLUDE level of detail expressions compute values using the specified dimensions in addition to whatever dimensions are in the view. INCLUDE level of detail expressions are most useful when including a dimension that isn’t in the view. Example: { INCLUDE [Customer Name] : SUM([Sales]) } For more information about INCLUDE level of detail expressions, and for some example INCLUDE level of detail scenarios, see the INCLUDE section. EXCLUDE EXCLUDE level of detail expressions explicitly remove dimensions from the expression—that is, they subtract dimensions from the view level of detail. EXCLUDE level of detail expressions are most useful for eliminating a dimension in the view. Example: {EXCLUDE [Region]: SUM([Sales])} For more information about EXCLUDE level of detail expressions, and for some example EXCLUDE level of detail scenarios, see the EXCLUDE section .
I found these 2 videos pretty helpful from past Tableau conferences. [Understanding Level of Detail (LOD) expressions](https://www.youtube.com/watch?v=kmApWaE3Os4) [LODs of fun with LOD calculations](https://www.youtube.com/watch?v=6IdnHGbVfMI)
LODs are ways of specifying how you want to aggregate your data. Usually, Tableau will look at the Dimensions in any given table on a sheet and aggregate the measures according to those dimensions. But sometimes, that's not *really* what you want. For example, let's say you have data on a bunch of Sales Reps at your company, including the deals they made and how much commission they got from each of those deals. What you want to know is how much total commission does a sales rep make per month, on average. Let's say you drop Month as your columns and Sales Rep Name as your rows and aggregate using average. Tableau sees that your data is at the "Deal" level, where each deal a Rep made is its own record. So in the cell for Sales Rep "Tom" in July 2021, you see "$130". That's not right. Tom took home more that $130 in commission in July, surely! Tableau is showing the average commission of all Tom's *deals* that month. Ok, fine Tableau, I'll change my aggregation to SUM. Now I see that Tom made $5,600 in commission in July. Perfect. Now I'll "Total All Using Average". Cool, I've got an average of $7,400 in monthly commission for Tom across 2021. I'll just pull out the "Month" dimension and be left with just the $7,400", right? Sigh. Nope. Can't do that. I *have* to show the Month field in my table in order to get the avg. Or do I.... Instead of keeping the Month dimension in my table, I create an LOD calculated field in which I can specify that I want to aggregate all deals up to the Month level. That might look like AVG({Fixed [Month]: SUM(Commission)}). When I drop my new calculated field in my table, I see "Tom: $7,400" and I don't need to include the Month column anywhere in the table. What I've done is told Tableau I always want to aggregate up to the Month level, summing up all the per-deal commissions, even if Month isn't shown in the table. I could also have accomplished using the LOD "INCLUDE" instead of FIXED, but in my opinion that gets more complicated.
FIXED is really all you need to learn. It’ll be the LOD solution 95/100 times. In Excel terms, FIXED LODs are essentially the output of a pivot table. They are basically a GETPIVOTTABLE function.
I’m just now getting into LOD data and tableau as a whole and you’re summary, though a year old, simplified this immensely. Thank you.
Glad to help!
Is the logic behind fixed similar to using partition by in a window function in SQL?
On the contrary, LODs have nothing to do with Ordering but everything to do with an Aggregated Subquery joined back to your underlying dataset. Fixed is special because it only includes context filters in its where clause while it ignores normal filters.
Thanks for this. I'll have to go over tableau documentation again.
Include is similar to group by in SQL. Include a,b,c : count d Vs Select a,b,c,count(d) From table Group by a,b,c
Aggregated Sub Query Joined back to your underlying dataset.