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!

3

Hi @MYNKJAIN,

Just checking back in since we haven’t heard from you in a bit. I wanted to see if the guidance shared earlier helped resolve your question, or if you found a solution in the meantime.

If you still have any additional questions related to your initial post, 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 @MYNKJAIN ,

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 Quick Suite Community Q&A Forum!