We've already considered aggregations such as sum, min, and max in Tableau. Often, you'll use fields as simple aggregations in the view. But sometimes, you'll want to use aggregations in more complex calculations.
For example, you might be curious to explore the percentage of the rent that was discounted. There is no such field in the data. It could not really be stored in the source, because the value changes based on the level of detail present in the view (for example, the percent discounted for an individual unit will be different to the percent discounted per floor or per building). Rather, it must be calculated at as an aggregate and recalculated as the level of detail changes.
Let's create a calculation named Discount % with the following code:
SUM([Discount]) / SUM([Rent])
This code indicates that the sum of Discount should be divided by the sum of Rent. This means that all of the values of Discount will be added and all of the values of Rent will be added. Only after the sums are calculated will the division occur.
Once you've created the calculation, you'll notice that Tableau places the new field under Measures. Tableau will place any calculation with a numeric result under Measures by default, but you can change row-level calculations to dimensions if desired. In this case, though, you are not even able to redefine the new field as a dimension. The reason for this is that Tableau will treat every aggregate calculation as a measure, no matter what data type is returned. This is because an aggregate calculation depends on dimensions to define the level of detail at which the calculation is performed. So, an aggregate calculation cannot be a dimension itself.
Now, create a couple of views to see how the calculation returns different results, depending on the level of detail in the view. First, we'll build a view to take a look at each individual rental period:
- Place Building, Room, Full Name, Start, and End on Rows.
- In the data pane, under Measures, double-click each of the following fields: Rent, Discount, and Discount %. Tableau will place each of these measures in the view by using Measure Names and Measure Values.
- Rearrange the fields on the Measure Values shelf so that the order is Rent, Discount, and Discount %:
You can see the percentage given by way of discount for each rental period. However, notice how the values change when you remove all fields except Building and Room:
Why did the values change? Because aggregations depend on what dimensions are defining the level of detail of the view. In the first case, Building and Room, Full Name, Start, and End defined the level of detail in the view. So, the calculation added up all the rent for each rental period and all the discounts for the rental period and then divided them. In the second case, Building and Room redefine the level of detail. So, the calculation added up all the prices for each building and room and all the discounts for each building and room and then divided them.
Notice that the values change again, as expected, if you look at the overall dataset without slicing by any dimensions:
