normally, you can use a calculated field in another calculated field. Unfortunately, it’s the sum()/sum() functions that are preventing you from doing what you want to do, the two sum functions are the aggregations that are causing the error message.

Perhaps if you could better explain what you are doing with all four columns, someone else smarter than me could find a solution for you. I’m sure if you replace “calculated value” in your second calculation with the sum()/sum()-1, your second calculation should work.

I’ve tried replacing as you suggested, and the error continues.

I’m migrating a huge query from metabase to quicksight that uses aggregations inside subqueries that have a filter. As we can’t alter the query with filter inputs, I’m bringing all of the data and letting quicksight filter it. Of course, the aggregations results inside the subqueries differ from filtering inside the subquery and outside.

As the problem is kind of complex, I’m trying to simplify it by stating that all I want to do is:

Calculate a value

Store this value as a raw number

Use this value inside a calculation

I assume this must be doable somehow…
Thanks again =)

Try simplifying it, take out the sum, I’m pretty sure Quicksight will aggregate it on its own. Sometimes you have to play around with the syntax to see what works. It really helps as well to line up your brackets.

I can’t take out the sum. It raises the following error:
“Mismatched aggregation. Custom aggregations can’t contain both aggregate (SUM) and non-aggregate fields in any combination”.