Cannot Mix Aggregate and Non-aggregate
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 –
It's just what we'd expect from a spreadsheet calculator
Tableau aggregates measures as they are brought to the viz so the individual profit percentages are summed in the viz – the problem started with the calculation – Sales and Profit need to be aggregated in the calculation
![](https://1.bp.blogspot.com/-JWeRGecK9ZU/XiSp97Q_LeI/AAAAAAAAEsM/JDLNGLbtIcso5ZEyIBkW_eeoNGD8VxfFgCLcBGAsYHQ/s1600/error%2Bmessage.png)
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 aggregated too – here Difference is a table calculation so Sales needs to be aggregated :
![](https://1.bp.blogspot.com/-wQJbhCHeIOo/XiTcxrsK3BI/AAAAAAAAEsY/GjCH1TgRDZo3eEW_dWtBaOgixUqU9H3DwCLcBGAsYHQ/s1600/Table%2BCalculation.png)
2-Embedded Dates
Often dates are embedded in a calculation that includes an aggregation –
![](https://1.bp.blogspot.com/-PkTujJuvBxY/XiTek0EyOpI/AAAAAAAAEss/WCyG5R3GxHsS6VkKSMbct1svu9ag4a8tgCLcBGAsYHQ/s1600/embedded%2Bdates.png)
Here Sales in aggregated so Order Date and Ship Date need to be aggregated – Yes aggregate Dates – sure Dates and string (text) dimension can be aggregated with Attr(), Min() or Max() – your choice will depend on the analysis you are doing
This is just one way around the problem
3 Aggregations caused by LOD's
Sometimes, dimensions within a LOD cause the problem – The argument in a LOD must be aggregated – here Sales is aggregated but Category is not
![](https://1.bp.blogspot.com/-E9qUhXV1L-k/XiTg9OO-0dI/AAAAAAAAEtA/qQJYgSvcasI8AL0e9aFb941dj-ZOSKjFwCLcBGAsYHQ/s1600/lod%2Bembedded.png)
There are 2 solutions – the first is to use a Min() or Max to aggregate the Category (Note: Attr() can not be used in a LOD)
![](https://1.bp.blogspot.com/-8y5SFtIGYb0/XiXB6PLi_mI/AAAAAAAAEtw/0huAbM6z-IUghN9BORccRLsi61mEcxERwCLcBGAsYHQ/s1600/LOD%2Berror%2Bmessage.png)
![](https://1.bp.blogspot.com/-V1xtsltlQVU/XiYZJmbRbbI/AAAAAAAAEuc/teGTczAHXPEIfkRukwGIiOthR47IEokUgCLcBGAsYHQ/s1600/max%2Bdate%2Bexample.png)
An alternative would be to use a table calculation (here Last()) but it also returns an error
![](https://1.bp.blogspot.com/-IRyTiIpmYYI/XiYbQFgEr6I/AAAAAAAAEu0/SkfQV7bdiaQp6q8uUzVxAG8-jKKNbHnvwCLcBGAsYHQ/s1600/last%2Bfor%2Bmax%2Bdate.png)
This can be resolved by aggregating Sales
- Open the calculation and drag the measures to the Marks card –
- IF they show up with an AGG() then they are already aggregated –
- 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
![](https://1.bp.blogspot.com/-E-d_PNk8xM0/XiYdrU6b5EI/AAAAAAAAEvI/r6s2GUz8yPUvah2W_wcuZ8-zYWlUHKbYgCLcBGAsYHQ/s1600/easy%2Bway%2Bto%2Bfind.png)
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
8 Responses
Great resource for teaching. Thanks Jim!
Thanks Ann – I'm doing a series on FAQ's from the Forum – stop back – the next is on Nulls
Thanks for sharing Jim. It really helpful.
Excellent post Jim – thank you!
I understand the issue, but it still catches me. Your tips to identify what are aggregates or not were particularly useful.
I recently voted on this idea to visually differentiate between aggregates and non-aggregates: https://community.tableau.com/ideas/10853
Hi Jim,
I wonder if you can help me understand and resolve a problem I am struggling with. I have about 10 years of data joined by date at the day level. For three different columns (say A, B and C) I have the a value that corresponds to the value on that date.
I have a bar chart display that has the date grouped by year and I want to show the "total" value from each of A,B and C that corresponds with the last day of that year where I have a date. So – for 2011 it might be 30-Dec-11 as that is the last day I have a value in that year. I know this because I have created MAX([Date]) and filtered by that date.
I simplistically thought I would be able to grab the corresponding value from A, B or C that exists in the column for the MAX(Date) at the year level, however, what I get is SUM([A] etc. ) which is the sum of all the values in A up until the last day of the year. A huge number!
I've tried, LOOKUP with INDEX and LAST etc. in a calculated field using MAX([Date])but am blocked in grabbing this atomic value from the column as it requires an aggregated value (SUM([Total])). I can resolve this, pretty sure, by creating a new column in my data source that shows only the additions or subtractions in A, B or C and not their "current" value for that date. In this case I think the SUM([A]) will correspond, but I figure there is more likely something I am fundamentally missing to implement this with the data structured as is.
Is my explanation clear? and if you have a moment, do you see something obvious you could point out to me?
Hi
Thanks great question – just reading the post it sounds like the data need to be pivoted – but without seeing the actual workbook it is a little difficult to provide a specific solution- Suggest you post your question on the Forum at https://community.tableau.com/community/forums and include your TWBX workbook –
Thanks
This comment has been removed by the author.
Thank you very much. This was so helpful! It solved the very issue I've been annoying for a long time…