Tableau Error: All fields must be aggregate or constant when using table calculation functions or fields from multiple data sources

By: Eric Parker

Pro Headshot.jpg

Eric lives in Seattle and has been teaching Tableau and Alteryx for 5 years. He's helped thousands of students solve their most pressing problems. If you have a question, feel free to reach out to him directly via email.

Have you gotten the error “All fields must be aggregate or constant when using table calculation functions or fields from multiple data sources” in Tableau?

99-1.png

First, let’s cover the difference between a standard calculation function and a table calculation function.

99-2.png

A standard calculated field function manipulates individual rows of data and creates a new column of data for use in Tableau.

A table calculation function allows you to compare, aggregate and calculate against data points in a worksheet. The key difference is that data points (i.e. bars, circles, cells of data) are already aggregated in Tableau.

If you aren’t sure if a function is a table calculation function or not, you can use the helper in the calculated field editor and go to the Table Calculation section.

99-3.png

Let’s look at this worksheet:

99-4.png

Notice that the dimension Sub-Category is on Rows and the SUM of Sales (aggregated) is on Columns.

If we want to know how the “Appliances” Sub-Category ranked by sales, we need to compare the SUM of Appliances Sales to the SUM of all other sub-categories.

As a result, our calculation shouldn't be RANK([Sales]).

99-5.png

But rather RANK(SUM([Sales])).

99-6.png

By adding that calculation to Label we can see that Appliances ranked 10th out of 17 sub-categories for sales.

99-7.png

If you’re still stuck on this error or running into another roadblock in Tableau, feel free to reach out or book an office hour so we can help!