How to calculate category‑specific Actual vs Budget % in QuickSight without aggregation errors

Q&A
,
1

I’m working with a dataset that has the following structure:

  • Dimensions

    • Category (8 distinct categories, all shown in the visual)

    • Department

    • Overall Group

    • Manager (for the overarching group)

  • Measures

    • Actual

    • Budget

Each row has one Actual and one Budget value.

Business requirement

For each Category, I need to calculate a percentage‑based metric comparing Actual vs Budget.
However, the formula definition depends on the Category:

  • For some Categories, the metric should be calculated as:
(sum(Actual) - sum(Budget)) / sum(Budget) * -1
  • For other Categories, the metric should be calculated as:
1 - ((sum(Actual) - sum(Budget)) / sum(Budget) * -1)

The results must:

  • Display correctly per Category

  • Roll up correctly when grouped by Department, Overall Group, or Manager

  • Avoid averaging percentages incorrectly

Problem

When attempting to implement this using a calculated field with conditional logic (for example, ifelse(Category = X, formula1, formula2)), QuickSight throws the following error:

“Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination.”

This occurs even though both formulas use aggregated measures (sum(Actual) and sum(Budget)).

What I’m trying to understand

  • How to structure a calculated field in QuickSight when:

    • The aggregation logic differs by Category

    • The metric must remain aggregation‑safe across rollups

  • Whether this requires:

    • Level‑Aware Calculations (sumOver)

    • Pre‑computed flags or logic moved into the dataset

    • Separate metrics per category type

  • Or whether this use case is fundamentally limited by QuickSight’s aggregation model

Key constraint

The solution must work within QuickSight’s calculated fields (not pre‑aggregated tables), and the metric must remain mathematically correct at multiple grouping levels.

Category Actual Budget Metric
Metric 1 Retention 2,150 2,000 108%
Metric 2 Rate 1,000 1,100 9%
Metric 3 Rate 1,400 1,600 13%
Metric 4 Retention 600 650 92%
Metric 5 Retention 1,700 1,800 94%

2

Hi @mariorat ,

Because you received mismatched aggregation errors, I would highly recommend checking out this resource and it is very good at explaining that specific error as well as ways to correct it. Here is the resource (Finding solutions for mismatched aggregation errors in Quick Sight calculations - Learn / Technical Articles - Amazon Quick Community). Check this out and let me know if you still have any questions!

3

I spent a good amount of time reading that article before I wrote my post.

And after you asked me to read it again, I realized my mistake was on the last formula

Industry % Enterprise Region= ifelse(min({Industry Measure})=1,{APJ Sales % Enterprise},{AMER Sales % Enterprise})

Once I added the min part, it worked. Thanks!