Tableau Class Notes: To Aggregate or Not to Aggregate?


Tableau Class Notes: To Aggregate or Not to Aggregate?

by Liz Jensen

Since I have started using Tableau Prep, I can’t overstate how helpful it has been for cleaning, shaping and exploring my data before I start visualizing it in Tableau Desktop. However, one concept eluded me at first, and that was the Aggregate step. Why add an aggregate step if we can visualize our data at whatever level of granularity we want in Tableau Desktop?

When to Consider Aggregation

Tableau has a great blog post elucidating this concept. It explains that if we want to change the structure of the final dataset, we might have to change the level of aggregation in our view, but I would add that this becomes especially crucial when we combine one table with another or when we want to share that data outside of a Tableau environment.

Additionally, if you’ve ever created a calculated field in Tableau Prep, you might notice that you can’t create an LOD expression, nor is there a blend option available in our list of steps. For all these use cases, you might want to consider using an Aggregate step.

Note: This example uses the Sample Superstore data source available in your Tableau Desktop repository, as well as a file called “Sample Superstore Regional Goals.xlsx” that has been packaged in the flow.

A Use Case for Aggregation

I’ll use a common use case. Let’s say we have data detailing the purchases of our customers. I also have a data source, but I want to see a simplified view of the data that will answer the following questions: what is each region’s sales, who is its sales manager, and what is that sales manager’s goal? Seems straightforward enough … until you start previewing your data sources and realize how differently the data sources are structured.

As you can see, our Orders table contains data for over 10,000 rows. Each row represents a different item in a customer’s order. This dataset also contains the Region for each order, which will be helpful since I want to visualize my Sales and Targets at the regional level:

Tableau Prep Orders table when aggregating

I also have my regional Sales Targets information in a different spreadsheet:

regional sales targets information in Tableau Prep

So what would happen if we joined these together and tried to visualize them in Tableau Desktop? Because our two data sources are at different levels of granularity, our Sales Targets would balloon—multiplying repeatedly for each row where that Region appeared. Since I want an output of just my Region, Sales and Targets columns, I know I will have to add an aggregate step for my Orders table, rolling up my Sales to the regional level:

aggregate step in Tableau Prep

Once I do that, it’s just a matter of adding a join step, and I can visualize my data exactly as I want. Because my two separate data sources are now at the same level of granularity, I will be able to get the desired output of Region-Sales-Sales Goal.

Yield the Best Output with Aggregation

This could be achieved with blending in Tableau Desktop or a Level of Detail calculation. But consider this: when you want to produce a clean, streamlined output in Tableau Prep, or that output might be consumed outside of Tableau Desktop (remember, you can export to a CSV!), adding an aggregate step is a must!

More About the Author

Liz Jensen

Analytics Consultant
Alter Your Analysis: Unofficial Holidays We know what dates Halloween, Christmas, and US Independence Day are on. But what about National Dogs in Politics Day or National ...
Alter Your Analysis: Women’s World Cup Match Wins In this second installment of Alter Your Analysis, I am tackling Alteryx’s Weekly Challenge 169: “Using the values in the attached file ...

See more from this author →

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK


Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Geschäftsführer: Mel Stephenson

Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072


Love our blog? You should see our emails. Sign up for our newsletter!