Curious about Tableau Aggregation? Tableau allows us to automatically aggregate data. Any time a Measure is brought out into the view, Tableau aggregates it. Tableau defaults to summing all values for that metric. But, there are other ways we can use to get different insights using Tableau aggregation.
How to Aggregate Data in Tableau
- To try different Tableau Aggregations, let’s open another tab and name it “Aggregations.” To see the number of items purchased per customer, drag Customer Name out to rows and double click Order Quantity.
- Notice how on the Marks card, we have a green pill that reads SUM(Order Quantity). This function is aggregating the Tableau data at a customer level. So, if I have purchased 10 items over the past year, my name would show up once. That is with an order quantity of 10.
- What if we want to change the level of Tableau aggregation? Say, from a sum to an average, and show the average number of items ordered per customer. To do this Tableau aggregation, click the down arrow of the green pill. There will be a box of a variety of options. In the middle, you’ll see Dimension, Attribute, and Measure (Sum). Click the arrow next to the Measure (Sum) and select Average.
- You’ll see that each customer has a number, like the image shown above. Now, you may be wondering why there isn’t the same number for all customers in the Tableau aggregation. For example, “customers order an average of 34 items when they purchase.” Remember we are aggregating the average in Tableau at an individual customer level, not the entire customer database. So, the Tableau aggregation says, if the customer has purchased five times and has bought 40 items in one visit, 30 items in the next and so on, then sum all items in all orders for that customer. Then, Tableau divides that by the number of items bought. This Tableau aggregation gives us the avg. number of items purchased of any given product.
- We can easily double-check this Tableau aggregation. First, let’s also order our data for easy analysis. Click the green Order Quantity pill on the Marks card. Then, click the Descending Sort button at the top of the screen.
Tableau Aggregate Measures
If you go up to the Analysis menu at the top, and uncheck Aggregate Measures, you’ll now see the breakdown of each customer’s individual activity.
- These steps can be helpful as de-aggregating the Tableau data allows you to view all the individual rows of your data source and provide insights. For example, in this data, we determine that Craig Rider has the highest avg. number of items purchased per visit at 47.50 items. But when we disaggregate Tableau measures, we see that he has only purchased twice, whereas other customers may have a lower quantity of items purchased per visit, but purchase from us all the time. This is valuable information that we can gather using Tableau aggregation, in order to determine strategy, pricing, special offers, etc.
- Tableau allows you to use multiple levels of aggregation in a single view. To do this aggregation in Tableau. click the chart with the X underneath the Story menu to clear off the entire viz.
- Drag Customer Name to Rows, then drag Profit to columns. The Tableau aggregation will default to a sum. Now, grab another instance of profit and drag it to columns again. Next, click the down arrow and change the Measure (Sum) to Average. Drag a final instance of Profit out to the columns shelf and change the Tableau aggregation to Maximum. We’ll now have three bar charts per customer showing their total profit, their average profit, and the maximum profit from them.
- Tableau has a number of other built in aggregations besides Sum and Average. You can get the Minimum and Maximum values in a data set, or you can do a count of everything in the data set.
- Certain data sources or Extracts will also allow you to get the Median value or Count Distinct. Medians are useful because they are outlier agnostic. So, let’s say I have sales values of $100, $200, $300, $400 and $10000. The average of those (i.e. the avg. sales amount) is $2200. But that’s not actually reflective of really anything in your data. The median just takes the middle value in the data set, which in this case is $300. That’s more reflective of the data you are trying to represent.
- Count Distinct is also a very useful Tableau aggregation as it allows you to count each unique instance of something. For example, I have 10 customers, each of whom has purchased 5 times. If I do a count, I’ll get 50 purchases. But if I wanted to know how many unique customers are buying from me, I wouldn’t want a count, I’ll run a Count Distinct to get 10.
- If you are more statistically inclined, there are other default Tableau aggregations that you can select, like percentile, variance, and standard deviation, that are also very useful in understanding your data.
If you want to explore the advanced tableau aggregation, be on the look out for our upcoming blogs or try our Tableau training, which is available in individual and corporate packages.