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.
Note 2: All aggregate functions work alike, we will only use SUM() as the examples for this series.
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.
Note: LOD worth it's own series in blog, so it will not be discussed here.
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
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.
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.