Calculating count of an aggregate

Question & Answer
,
1

Hi,

I’m continuously designing some KPIs based on an underlying data set. Using a transactional data set with e.g. customers / products we are in this specific case looking to count the number of occurences of e.g. a customer having an aggregate margin that is negative. The number of customers that have “at least once” in the selection had a negative margin can easily be defined as:

distinct_countIf(customer_id, {line_margin} < 0)

But when we are instead curious about counting the customers with an aggregate margin and try to do the following:
distinct_countIf(customer_id,sum({line_margin}) < 0)

It is not supported by Quicksight:
“Nesting of aggregate functions like DISTINCT_COUNT and COUNT(DISTINCT CASE WHEN SUM(“line_margin”) < 0 THEN “customer_id” ELSE NULL END) is not allowed.”

Any thoughts? For example just displaying over a time period that this has been the number of customers / articles where the aggregate across all transactions applicable has been negative. I.e. not any tables but simply a single KPI visual with the:
the number of customers with negative margin
the number of article with a negative margin

Any help is appreciated!

2

Hi @DanielJansson, to create KPIs for the number of users or articles with an aggregate negative margin, we need to work around the limitation of not being able to nest aggregate functions. Below is an approach, see if that works – I’m using the SumOver as a one-trick pony! Also, explore these specialized functions – click here. Keep it simple. At times, the best place is SQL to handle complex calculations.

Here’s a step-by-step approach to achieve this using calculated fields and filtering:

Step 1: Create a Calculated Field for Total Margin per User

  1. Create a new calculated field for the total margin per user. This can be done using the following formula:
sumOver({line_margin}, [user_id])

This calculated field will sum the line_margin for each user.

Step 2: Create a Calculated Field to Identify Negative Margins

  1. Create another calculated field to identify if the total margin for each user is negative. Use the following formula:
ifelse(sumOver({line_margin}, [user_id]) < 0, 1, 0)

This field will return 1 if the total margin for a user is negative and 0 otherwise.

Step 3: Aggregate the Negative Margin Indicator

  1. Create a third calculated field to sum the negative margin indicators. This will help in counting the number of users with negative margins:
sum(ifelse(sumOver({line_margin}, [user_id]) < 0, 1, 0))

Step 4: Create a KPI Visual

  1. Add a KPI visual to your analysis and set the aggregated negative margin indicator as the metric. This will display the count of users with a negative margin.

For Articles

Follow the same steps but replace user_id with article_id to calculate the number of articles with a negative margin.

Detailed Steps for QuickSight Implementation:

  1. Calculated Field for Total Margin per User:
  • Name: Total_Margin_Per_User
  • Formula:
sumOver({line_margin}, [user_id])
  1. Calculated Field to Identify Negative Margins:
  • Name: Negative_Margin_Indicator
  • Formula:
ifelse(sumOver({line_margin}, [user_id]) < 0, 1, 0)
  1. Calculated Field for Summing Negative Margin Indicators:
  • Name: Count_Negative_Margin_Users
  • Formula:
sum(ifelse(sumOver({line_margin}, [user_id]) < 0, 1, 0))
  1. KPI Visual:
  • Drag the Count_Negative_Margin_Users calculated field to the Value box of the KPI visual.

For Articles:

Repeat the above steps with article_id instead of user_id.

Notes:

  • Ensure that the data preparation steps are correct, especially the granularity of your data set.
  • Validate the intermediate calculated fields to ensure they are computing as expected before adding them to the KPI visual.
  • If you encounter performance issues, consider optimizing your data set or using SPICE for faster computations.

This method avoids nesting aggregate functions directly and leverages calculated fields to achieve the desired KPIs.

Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!

One more thing, at AWS, our roadmap is primarily driven by our customers. Your feedback helps us build a better service. I have tagged this as a feature request. Please keep an eye on the What’s New / Blog. You can set up a Watching Alert by clicking on the bell icon.

3

Hi @Xclipse,

The solution you’ve suggested does not seem to be working for multiple reasons. First off the formula sumOver({line_margin}, [user_id]) requires that “Field “line_margin” must be aggregated for table calculations.”. Secondly, adding a sum({line_margin}) within the sumOver creates some typical issues as you then when trying to do step 3 get caught in the same problem as previously:

“Nesting of aggregate functions like SUM and SUM(CASE WHEN SUMOVER(SUM(“line_margin”), “user_id”) < 0 THEN 1 ELSE 0 END) is not allowed.”

Finally even if this was supported then you are using one of the Table Calculation functions (sumOver) meaning that it wouldn’t really display very well in a KPI visual → adding user_id in this scenario to “trend field well” would display a single user_id only and the sum of that one’s function
(either 0 or 1 and not the # of users across the dataset).

If we are talking generally then this becomes a problem as you’re not able to dynamically generate calculated metrics that rely on “intermediary tables”. I.e. Imagine that you could’ve created a virtual table from the original dataset which then any operation can be applied towards. Similar to nesting queries in a SQL structure. If this is on your roadmap currently then I would be very interested in knowing :slight_smile:

4

Hi @DanielJansson,

Thanks for your feedback! For calculating the count of an aggregate without running into issues with nesting aggregate functions, you can try using calculated fields and pivot tables.

Here’s an approach:

  1. Create a calculated field for the line_margin using an IF condition to identify positive or negative margins.
  2. Use a pivot table to aggregate the data by user_id and calculate the sum of line_margin for each user.
  3. Apply a custom SQL query if possible, to perform the intermediate aggregation before bringing the data into QuickSight.

This method avoids the nesting issue and allows for better visualization in KPIs.

In case you need further assistance with your problem, please create a sample dashboard with sample dataset showing your problem state using Arena and post it here. (Details on using Arena can be found here - QuickSight Arena

5

Hi again @Xclipse,

Thanks for the answers and attempts. I think that it’s then fairly clear that this is not currently possible. There are multiple reasons why you would want to avoid using a pivot as then you are not aggregating but rather distributing the results over multiple lines → a user would have to go through the table on a row-to-row level to see if the user / customer / article has a negative margin and as such it is no longer a KPI but rather a “table” of user / customer / article profitability.

Let’s consider this thread closed as it’s simply not possible. Using custom SQL logic for us breaks a quite nice flow of data from Athena and would introduce customizations in a layer that is standardized. Additionally if you parametrize the custom SQL then you get Athena loading times for end quicksight users which can reasonably be in the range of 5-15+ seconds which would be a quite poor UX. :slight_smile:

/Daniel

1 Like