Most calculations happen at a specific level, such as the sheet level, table row level, and so on. Level of detail expressions let you specify the level at which you want your calculation to occur.
- [Narrator] One terrific Tableau capability is the level of detail expression. Most calculations happen at a specific level, such as the Sheet level, Table Row level, and so on. Level of detail expressions let you specify the level at which you want your calculation to occur. In this movie, I will show you how to define level of detail expressions and give an example of their use. The visualization that I'm working with is contained in the 04_05_LOD workbook, and you can find it in the Chapter Four folder of your Exercise files collection.
This visualization already contains one calculated field, and that is Total Price, which you can see here under Measures, and Total Price finds the quantity multiplied by the price of a particular order item. So, if I drag Total Price to the data area, you can see the total of all of those values. But now, let's say that I want to find the Total Price minus the Average of the Total Price. In other words, I want to find how far above average each of these Product Categories is.
To do that, I need to create a new calculated field. So, I will go to the Analysis menu, and click Create Calculated Field, and for Calculation One, or for the Name, I will type DiffFromAVG and then click in the Editing area. So, again, what I want to do is to calculate Total Price minus the Average of the Total Price. So, I will enter the Total Price field first with a left square bracket, and then Total Price, and then I will subtract the Average, so that's AVG, of the Total Price field, so again, I'll type that, a left square bracket, followed by capital T, then I've got Total Price, press Tab, and there I am.
The problem is, as you can see at the bottom left, is that the calculation contains errors, and if I want to see why, I can click the error down arrow here, and it says that I cannot mix aggregate and non-aggregate arguments with this function. Okay, so what that means is that I'm trying to take the Total Price for a particular item or particular cell within my table and then work with the average of all total prices.
In other words, all of the data at the data set or Sheet level. So, I can't mix the two, except that I can. And the way that I do it, is by creating a level of detail expression and to do that I will click to the left of the A in the AVG, and then I will type a curly bracket. So, there's a left curly bracket there, and then to the right of the Average function's right parentheses, I will type a right curly bracket and you can see that the calculation is now valid.
So, I have Total Price and I'm subtracting the Average of Total Price which is at a different level of detail and the fact that I enclosed the Average calculation inside of curly brackets lets Tableau know that it needs to pull the values from a different level. If I click Okay, then I have created my field, and I will drag Difference from AVG into the data area, and you can see that I have Difference from AVG on top and Total Price on the bottom.
You can see that Grid Tie Inverters are way above the average, which makes sense, they are the most expensive items, so even if you only sell a few it won't do that much. Batteries hold their own pretty well, and then Landscape Lighting is well below, Lightbulbs are well below, same thing for Solar Panels, and Wind harvesters. So, if you ever need to compare data from two different levels, one at the aggregate level, and one at the Table cell level, using a level of detail expression will allow you to do so.
- Managing data sources and visualizations
- Managing Tableau worksheets and workbooks
- Creating custom calculations and fields
- Analyzing data using statistical tools
- Sorting and filtering Tableau data
- Defining groups and sets
- Creating and pivoting crosstabs
- Formatting Tableau visualizations
- Creating basic charts
- Annotating and formatting charts
- Mapping geographic data
- Creating dashboards and actions
Skill Level Beginner
Creating Interactive Dashboards in Tableau 10with Nate Makdad2h 24m Intermediate
Integrating Tableau and R for Data Sciencewith Ben Sullins1h 10m Intermediate
1. Introducing Tableau
2. Manage Data Sources and Visualizations
3. Managing Tableau Worksheets and Workbooks
4. Creating Custom Calculations and Fields
5. Analyzing Data Using Statistical Tools
6. Sorting and Filtering Tableau Data
7. Defining Groups and Sets
8. Creating and Pivoting Crosstabs
9. Formatting Tableau Visualizations
10. Creating Basic Charts
11. Annotating and Formatting Charts
12. Mapping Geographic Data
13. Creating Dashboards and Actions
Next steps1m 7s
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.