Why I cannot use new Calculated field with "SumOver"/"Sum" function in other calculation?

[heichitc]

It shows error when I use the calculated field in calculation with other fields, both of them are numbers. How can I subtract them?

[David_Wong]

Hi @heichitc,

The general rule is that you can’t mix aggregated values and non-aggregated values in the same calculated field. There’s a section in this article that goes over mismatched aggregation errors.

Level-Aware Calculations: Tips and Best Practices

[Level-Aware Calculations in Amazon QuickSight] Level-Aware Calculations (LAC) are powerful functions that allow us to perform calculations at a granularity level which is different from the granularity of our datasets and/or visuals. The sample dataset referenced by this article is a dataset of product orders placed by customers. Each order can contain multiple products and each product in an order has a unique Line Item ID. Each row of our dataset represents one line item but we want to per…

This second article also covers the topic of mismatched aggregation.

Finding solutions for mismatched aggregation errors in QuickSight calculations

Finding solutions for mismatched aggregation errors in QuickSight calculations Mismatched aggregation errors are common QuickSight calculation errors reported by users. This error occurs when the calculated field contains both aggregated and non-aggregated (row-level) values. For users new to QuickSight, it’s not always apparent what causes this error. It is important to understand what is aggregated vs non-aggregated from a QuickSight perspective. This article explores common cases where these …

If you can describe what you’re trying to calculate, I can help you come up with the correct calculated field.

[heichitc]

In this case, there will be no ways to compare the value between them?

[David_Wong]

Can you try this?

sumOver(quantity, [part_model_name], PRE_AGG) - quantity

[Brett]

Hi @heichitc,
It’s been awhile since we last heard from you; did you have any additional questions or were you able to find a solution from the assistance provided above?\

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

Thank you!

[Brett]

Hi @heichitc,
Since we haven’t 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 any relevant information that may be needed.

Thank you!