Sum of Aggregations

Q&A
, ,
1

We’re building a capital program dashboard in QuickSight and we’re running into an aggregation issue.

Goal: In a table visual at the project level, we need to show:

  • Fund

  • Project

  • Projected Budget (from a cashflow table; aggregated by cashflow_pk; monthly grain)

  • Actual Cost (from a budget/actuals table; aggregated by budgetline_id; monthly grain)

  • Total Cost = Projected Budget + Actual Cost

Problem: The two measures (Projected Budget and Actual Cost) come from different tables, are at a monthly grain, and roll up using different primary keys. When we try to create a single “Total Cost” field, QuickSight throws aggregation errors.

What we tried:

  1. A calculated field using sumOver() - results in an error

  2. A calculated field that directly adds the two measures - error stating you can’t add values with different aggregations

Has anyone successfully handled this pattern (two measures from different datasets/keys) to produce a single sum metric at the project-level table?

2

Hi @kamalpreet,

Welcome to the Quick Community! Based on your description of your use case, this is currently not possible since your tables come from two different datasets. I would recommend seeing how you can combine both datasets into one during dataset prep, and then try a calculated field solution from there.

Let me know if this helps!

3

Hi @kamalpreet,

Just checking back in since we haven’t heard from you in a bit. Were you able to see my most recent reply and/or find a solution yourself in the meantime? If you still have any additional questions related to your initial post, please feel free to share them. Otherwise, any update you’re able to provide within the next 3 business days would be helpful for the community.

Thank you!

4

Hi @kamalpreet,

Since I haven’t received any further updates from you, I’ll treat this inquiry as complete for now. If you have any additional questions, feel free to create a new post in the community and link this discussion for context.

Thank you!