Aggregation in Tableau - Part 1 - Aggregate (AGG) functions

by Liu Zhang
Exam
Photo by Chris Liverani / Unsplash

Have you ever being frustrated by Tableau's warning message?

Cannot mix aggregate and non-aggregate arguments with this function

What is your response? Try every possible click on the screen possible?

Let's not rely on our luck, even if it worked, it will only cause more trouble down the road.

In this series, we will discuss how Tableau treat it's values (link) and how we can better handle the above warning message (or avoid it in the first place).

Let's start with a very simple table.

[Sales] will give individual row value with respect to the granularity of data in the view, i.e. 1, 2, 3 for each fruit in the view. While SUM([Sales]) will be aggregated value of individual [Sales], so it will be column total of 6.

So what's wrong with the formula to find a percentage?

[Sales]/SUM([Sales])

Although it makes sense in an Excel table, it does not work within Tableau as they are in different level of granularity, we will need additional table calculation or LOD to get the required results.

Note 1: We will keep the default setting SUM for aggregate measures.

Tableau's default setting is to keep aggregation ON.

Note 2: All aggregate functions work alike, we will only use SUM() as the examples for this series.

Notice the SUM() in Measure Values

Since we have Name to break the view (create a header), so Tableau aggregate values for each entry in Name, as there is only one value for each fruit, the sum of sales for each fruit is same as the input table.

To find the total column sum of sales, there are two ways we can do.

Level of Details calculation

Note: LOD worth it's own series in blog, so it will not be discussed here.

Table calculation

Total is a table calculation function, used precise in this situation to deal with column aggregation, it uses whatever the row values here (i.e. SUM([Sales]) and perform a sum.

Notice the yellow highlight region, it indicate the direction of the sum is being considered. Although it say Table (across), it is better to understand as such

Find the total of row (across table) values
Result

Note: In Measure Values, Sales - Total is not being aggregated, as it is already an aggregated value with respect to the whole table.

Once we have the required values for the given granularity, we can calculate the initial question about sales ratio.

Result

Hopefully the table output will provide an easy starting point to understand aggregation in Tableau. The next part of the series will cover a much more common function ATTR(). Stay tuned.


Looking for more guides, tips and tricks in Tableau or Alteryx? Go check out the other blog posts from the Data School.

Tableau Public

LinkedIn

Twitter

Thu 22 Jul 2021

Fri 18 Jun 2021

Wed 14 Jul 2021