Avg function with 2nd argument (calculation dimension) is not working when creating the calculated field at Dataset level

Question & Answer
,
2

Hi @sharmsw, welcome to the QuickSight Community, when you’re working with calculated fields and functions like AVG, it’s crucial to ensure that the function usage aligns with its defined syntax and the context within which you are applying it. The error you are encountering, “Function AVG should have 1 argument instead of 2 arguments,” typically indicates that there is a misunderstanding in how the function should be applied in your formula.

Here’s how you can correctly use the AVG function in QuickSight to calculate the average of sales at the customer level:

Step-by-Step Solution

  1. Ensure Correct Syntax: The AVG function in QuickSight takes only one argument, which is the field or expression over which the average needs to be calculated. Ensure your expression looks something like this:
AVG(sales)

Here, sales should be the name of the field containing the sales data.

  1. Level of Aggregation:
  • If you’re trying to calculate the average sales per customer-field directly in a calculated field without specifying a grouping in a visual, it might lead to errors because calculated fields are computed without inherent grouping.
  • To fix this, you can use the average calculation directly in your visual by dragging the sales field into the value field and setting the aggregation to Average. Then, ensure your customer-field is used as a dimension in your visual, which automatically calculates the average sales per customer-field.
  1. Creating a Level-Aware Calculation: If you need to use this average in other calculations or in places where you can’t directly set visual-level aggregations, consider creating a level-aware aggregate. In December 2020, QuickSight introduced level-aware calculations that allow you to specify the level at which an aggregation should happen. The syntax would look something like this:
avgOver(sales, [customer-field], PRE_AGG)

In this example, sales is averaged over each customer-field.

  1. Using the Calculation: Once you have your calculated field correctly set up, you can use it across different visuals in your analysis, ensuring that the context (or level of detail) of each visual aligns with how the calculated field is supposed to work.

Common Pitfalls

  • Wrong Field Type: Make sure that the sales field is numerical. Calculated fields will fail if the underlying data type isn’t compatible with the mathematical function.
  • Misplaced Arguments: Do not add an additional argument in the AVG function. It should contain only one argument (the field you want to average).

If you have tried all the above and still face issues, consider checking this QuickSight community forum or the AWS QuickSight documentation for more specific guidance tailored to your dataset and configuration.

Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!