Nested Calculations - summing on criteria

I have a dashboard where my stakeholder wants a filter where they are capping on a sales amount.

This option, when “Yes”, should show all cases, any cases over $10k would be showing with a max value of $10k.
When “No”, it should show all cases with no capping at all, and should be the default

  • Example, if an agent has two cases, $3,000 and $15,000, that total $18,000 in premium
    • With capping on, it would show $13,000 (The $10,000 + $3,000)
    • With capping off, it would show $18,000

A $13k case is added in as a $10k case. Any case above $10k, only the $10k portion is added to the totals.

Conceptually, I understand what I need to do, but Quicksight seems to not like any of the nested aggregations.

Hey willowfae,

First I would make a paramater that you can change from “Yes” to “No”. Let’s call it CappingParam.

And add a control so you can change it from Yes to No.

Then here would be the calculated field.

money = ifelse(${CappingParam}=“Yes” AND case_amount>=10000,10000,case_amount)

This will check if their is capping on and the amount is greater than or equal to 10000 then return 10000. Else, all other situations, would return the normal case amount.

Let me know if that’s what you’re looking for.

I am working on getting this to work in my dataset, however, as I read it will it only return the $10,000 and not the total.

I wound up creating a new column and my stakeholder approved but this was still very helpful. Thank you!

Yes, this calculated field will only return the amount. You would then use this calculated field and sum on it by your preferred group.

If you make a table with the agents as rows and this calculated field as the value. You would sum this value and get your total.