How to handle QuickSight Error: “Cannot Mix Aggregate and Non Aggregate function" & "Nesting of Aggregate function"

Question & Answer
, , , ,
1

Hi QuickSight Team,

As a QuickSight user all of us are facing this two error like (“Cannot Mix Aggregate and Non Aggregate function" & “Nesting of Aggregate function”) in our day to day work. So can you create one articles on this topic like how to handle when we have facing this type of error then it would be greatly appreciated .

Thanks & Regards
Biswajit Dash

2

Hi @Biswajit_1993 - Can you please provide more information on this, and give some examples and screenshot so that we all can discuss and come up with a solution.

Tagging @Max @Bhasi_Mehta @David_Wong @Kristin for more reach.

Regards - San

1 Like
3

Hi @Sanjeeb2022 Thanks for responding actually this is a common error when we are working with calculated field for creating any custom field for our analysis so on the example part(for everyone the calculation is different but the error message is same) we have a list of example but what I need need a simple solutions for all, like if the issues on this then we need to follow these steps to avoid the error in your calculations.

Thanks & Regards
Biswajit Dash

1 Like
4

Thanks @Biswajit_1993 . I will discuss with you on this and if require I will submit a ticket to AWS as well.

Regards - San

1 Like
5

I think the “Cannot mix aggregate and non aggregate function” error usually happens when users don’t realize that their visual is at a grain which is different from what’s in the dataset.

Here I have a dataset where each row represent a line item in an order placed by a customer. For example, there are 3 rows for order ID APJ-2018-112326 because that order contains 3 line items.

image
image1218×413 59.3 KB

Let’s say I want to calculate the profit as a percentage of sales on each order (not on each line item), so I need to aggregate my data at the order level, i.e. I need to remove all fields related to line items from my visual.

image
image765×257 27.8 KB

When I use {Profit} in a calculated field, it refers to the value in one row of the dataset. We saw previously that some orders have multiple rows in the dataset. If I create my calculated field without using an aggregation with {Profit}, QuickSight doesn’t know from which row to get the value.

image
image959×146 4.9 KB

What the message is basically saying is that you can’t reference a value in one row and also have aggregations. All arguments in calculated fields have to be single values or they all have to be aggregations.

To combine multiple values of {Profit} into one single value for each order, I need to use an aggregation like sum.
{Profit} % = SUM({Profit}) / SUM({Sales})

image
image838×333 40.1 KB

This issue isn’t specific to QuickSight. Other BI tools like Tableau and Power BI also don’t allow that.

Tableau: Cannot mix aggregate and non-aggregate arguments with this function.

Power BI: A single value for column ‘Profit’ in table ‘Sales Data’ cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

I think the error message in QuickSight could be improved to make it easier to understand why it’s not allowed. The explanation in Power BI is pretty good.

2 Likes
6

Hi @David_Wong Thanx for responding my query, that’s what my intention is on QuickSight. Because I already worked with Tableau and in Tableau there are lot of articles present on tis topic. So like this I requested if any blogs/article for this topic will always help us to understanding the things in more clarity.
But yes as per your above example it provide a good idea on this topics.

Thanks & Regards
Biswajit Dash

7

@Biswajit_1993
I just posted an article on level-aware calculations. It includes a section on mismatched aggregation errors.