Aggregate base on the condition

Q&A
,
1

My dataset is like this:

metric_name, Denominator, Numerator

For metric name= M1, I want to use sum(Numerator)/sum(Denominator)

For metric name= M2, I want to use sum(Numerator)/MAx(Denominator)

I am trying to write something like this:

ifelse(
contains(metric_name, ‘Contri’),
sum(num_wk0) / max(dnm_wk0),
sum(num_wk0) / sum(dnm_wk0)
)

but getting error– QuickSight doesn’t allow mixing aggregated fields (like sum(), max()) with non-aggregated fields (like metric_name) in the same calculated field.

how to fix it?

2

Hi @MYNKJAIN ,

Mismatched aggregation is a common error for users. I would recommend referring to this resource and it talks through common mismatched aggregation use cases and potential solutions for each of them. Here is the resource (Finding solutions for mismatched aggregation errors in Quick Sight calculations - Learn / Technical Articles - Amazon Quick Suite Community). Check it out and let me know if this helps!