Using a Level Aware Calculation with an Aggregate in a Calculated Field

Question & Answer
,
1

I am trying to create a calculated field for a visual that will show a ratio of defective units per total total units produced broken down by the defect type. I would like the denominator to show the total volume produced over all and not by the defect_type. I would like the numerator to calculate defective units broken down by the defect_type.

Here is my current calculated field:
sum({defective_units }, [{defect_type}]) / sum({total_units},[ ] )
I have also tried:
sum({defective_units }, [${visualDimensions}]) / sum({total_units},[ ] )

QuickSight allows me to save the calculated field, but once it is added to the visual I get the same error message for both: “The Level Aware Calculation (LAC) aggregate functions inside one visual aggregate functions should always share the same grouping key”

I understand the error message, so I am looking for other ways to get the same result. Are there any other ways to achieve this type of calculation?

Thank you.

For additional context, I got the numerator and denominator working as columns. I feel like it is so close to working!

image

2

Instead of sum’s can you try sumOver?

sumOver({defective_units }, [${visualDimensions}]) / sumOver({total_units},[ ] )
3

@baramichelle

We have not heard back from you regarding your question. We would still like to help. If we do not hear back in the next 3 days, we will archive the question

4

Changing the calculation to sumOver did not work. I am still getting the same error message.

5 
sumOver({defective_units }, [{defect_type}]) / sumOver({total_units},[ ] )

This gives you the exact same error?

1 Like
6

Sorry not the same exact error. In this case the error message appears and prevents me from saving the calculated field with the new formula.

This is the exact text, “Field “defective_units” must be aggregated for table calculations.”

7

Can you try one of these? I think either one should work but with the first one the percentages will always add up to 100% even if you apply filters.

sum({defective_units}) / sum({total_units}, [ ])
sum({defective_units}) / min(sumOver({total_units}, [ ], PRE_FILTER))

2 Likes
8

Hello @baramichelle ! Did David’s response answer your question?

We would still like to help answer your question, but we haven’t heard from you in a few days. If we do not hear from you in 3 days this topic will be archived.

9

Hello @baramichelle !

This question is being archived, if you still need help please reach out in the community.

10