Calculating count of an aggregate

Question & Answer
,
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