FAQ Series- Cannot Mix Aggregate and Non-aggregate

 Cannot Mix Aggregate and Non-aggregate 

Look familiar:

One of the most frequent questions we see on the Forum results from trying mixing aggregate and non-aggregates in the same calculation - But why is that a problem and how do you resolve it -

Next time you load a data file into Tableau, open the data source tab and spend a moment to look at the data structure.  This might look like a spreadsheet 

Now, open a worksheet and make a calculation to determine the Profit Percent as Profit / Sales 

Return to the data source tab and a Column (Measure) has been added to the table with the results of the calculation for each "Row" of data - It's just what we'd expect from a spreadsheet calculator 

Go to the worksheet and make a viz with totals and subtotals.  Whats going on? The row profit percents are correct but the totals and subtotals are wrong

Tableau aggregates measures as they are brought to the viz so the individual profit percents are summed in the viz - the problem started with the calculation - Sales and Profit need to be aggregated in the calculation

Now on the Data Source tab the Aggregate Profit Percent look just like the Simple values

But when we add the new measure to the viz we get the correct totals and subtotals

Note also the measure is brought to the viz as and (AGG)regate

So you can see how using aggregation in a calculation will affect the result but it is also the source of the aggregate - non-aggregate problem.  

The message simply means that if one dimension or measure is aggregated in a calculation then ALL the measures and dimensions in the calculation must be aggregated 

- But sometimes it is not easy to see which dimension or measures need to be aggregated.  See the following examples:

1-Table Calculations

Table calculations are aggregations so other measures in the calculation need to be aggregate too - here Difference is a table calculation so Sales needs to be aggregated :

Aggregating Sales with Sum() solves the problem 

2-Embedded Dates

Often dates are embedded in a calculation that includes an aggregation - 

Here Sales in aggregated so Order Date and Ship Date need to be aggregated - Wait aggregate Dates - sure Dates and string (text) dimension can be aggregate with Attr(), Min() or Max() - your choice will depend on the analysis you are doing

This is just one way around the problem

Note: removing the sum() from sales would also work

3 Aggregations cause by LOD's

Sometimes, dimensions within an LOD cause the problem - The argument in an LOD must be aggregated - here Sales is aggregated but Category is not 

There are 2 solutions - the first is use a Min() or Max to aggregate the Category (Note:  Attr() can not be used in an LOD)

The second is to move the Sum() outside the conditional statement in the LOD

Either solution will work

4 Using an LOD to resolve an aggregation problem

This example calculates the COGS percent to Sales

determine  COGS:

But it results in an error when used to calculate the percent to Sales

LODs create a virtual layer in you data set that is at a different level than the data itself BUT they are not an aggregate.   To correct the error aggregate the LOD

5 Value at Max Date

A common question is to find the value on the last date - there are 2 ways - My preferred is using an LOD to find the last date in the data for each sub-category

Then the sales value on that date is 

But it returns and error because the Sales are aggregated but neither the order date nor the LOD are - so a solution would be to aggregate the date and the LOD - here I used Min 

An alternative would be to use a table calculation (here Last()) but it also returns an error

Which can be resolved by aggregating Sales

Process to Identify the Aggregate

There could be many more examples and still not get to the one you has you stumped - Fortunately there is an easy way to know which of the dimensions or measure in your calculation are aggregated and which are not - 

• Open the calculation and drag the measures to the Marks card - 

• IF the show up with an AGG() then they are already aggregates - 

• If Tableau tried to aggregate them with SUM()  then they are not aggregated and you will need to decide which aggregation best fits your analysis

Hope this helps - If you have specific examples where you need some help feel free to ping me here or add a post to the Forum

Enjoy
Jim