I need… a Level of Detail Calculation
Intro
Level of Detail calculation changed how Tableau Desktop worked by allowing you to set a calculation’s level of aggregation based on what you wanted instead of what discrete fields were in the view you were creating. You might be asking what this has to do with Tableau Prep, well a few reasons:
Mimicking what you’d do in Tableau Desktop whilst you are prepping the data
Simplifying the data set for other users
Completing calculations with different levels of aggregation whilst not having to use joins to reconnect the data at different levels of granularity
In Tableau Prep Builder, you have the benefit of using the visual calculation editor to help you form your Level of Detail calculation.
The scenario
You want to work out what percentage of each order’s sales contributes to the regional total sales.
The steps
Input the Orders table from Superstore Sales
2. Add an Aggregate step by clicking the + symbol on the right side of the Input step
3. Use the Aggregate step to create a data set with each order’s sales total and the region it is in. Add Order ID and Region to the Group By section of the Aggregate step and sum Sales on the Aggregated Fields section.
4. Add a Clean step after the Aggregate step
5. Create your Level of Detail calculation by clicking on the Sales field ellipsis menu in the Profile Pane, selecting Create Calculated Field, then select FIXED LOD.
6. Change the aggregation of the Level of Detail calculation to Sum. The default aggregation is Min in the image below.
7. Add a Group By selection and select Region
8. Rename your Level of Detail Calculation to Regional Sales Total. Double-left click the Calculation1 name in the top left corner of the visual calculation editor and enter the new name. Click Done in the top right corner to save your calculation.
You’ve now created your Level of Detail calculation so you can use it to form what percentage of sales each order makes up for it’s region.
9. In the same Clean step, click on the ellipsis menu icon on the Sales field again, select Create Calculation Field, then select Custom Calculation.
10. In the text calculation editor, create the calculation and rename the calculation Order as a percentage of Regional Sales:
[Sales] / [Total Regional Sales]
Outcome
You now have a new field that is a percentage of the regional sales for each row in your data set. Each row represents one Order ID.
When used in Tableau Desktop, the Order as a percentage of Regional Sales field can be formatted to be a percentage. This will turn the value 0.30 in to 30%.
