I am trying to calculate based off my excel formula for CTM but I just end up with zeros

So I have this excel formula:
=(((total_compliant_qty_confirmed+[@[Not Compliant]])/total_qty_confirmed)-overall_compliace)*10000

And I tried to transition it into a quicksight formula:

(
    (
        (
        {Total Compliant Units}+sumOver(sumIf({Total Qty Confirmed}, Status <> "Complaint"), [Manufacturer])
        )
    / sum({Total Qty Confirmed})
    ) 
- Compliance
)*10000

But I just end up with crazy numbers, I broke down each section to get the overall formula but still doesn’t work

Your SumOver grouped by Manufacturer seems different from your excel formula.

Please share some sample details of your excel data and the same from what you see in QuickSight. It would be easy to answer then

Hi @mcskylar

Error in your calculation indicates a mix of aggregated and non-aggregated fields in your formula. To resolve this issue, we need to refactor the formula to ensure that all components are either fully aggregated or non-aggregated.

Please try the following calculated field.

Example: Replace the fields from your dataset

(
    (
        sum({Total Compliant Units})
        + sumIf({Total Qty Confirmed}, {Status} = "Not Compliant")
    )
    / sum({Total Qty Confirmed})
    - avg({Compliance})
) * 10000

Hi @mcskylar,
It’s been awhile since we last heard from you, did you have any additional questions regarding your initial post?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

Hi @mcskylar,
Since we have not heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you!