Aggregate a custom Calculated Field for average

In excel, I have a column that is calculating a cumulative average of the average values in Column A using the formula =AVERAGE(A$2:A3) in column B.

The values in Column A correspond with my custom calculated field labeled Column A, but when I create a new calculated field for the cumulative average (column B) using the following (I tried runningSum and RunningAvg as I am not sure which I should use), I get an error that nesting aggregate function isn’t allowed:

  sum({Column A}),
  [truncDate("MM",{reporting_period}) ASC]

When I remove the brackets, I get zeros.
I think my problem might have to do with certain limitations imposed on “custom aggregations” within QuickSight because I have not found a way to aggregate a custom aggregation…or something like that?

Could someone help me figure out the right way to do this?

What is your calculated field for column B? Another running sum function?

1 Like

Hi Max,

Column A’s custom field is:
sum({Measure_Field})*sum({Measure_Field2}) / (sum({Measure_Field2})^2)

I’d like to create a custom field for column B which in Excel uses =AVERAGE(A$2:A3)

Have you tried taking the avgOver?

avgOver(sum({Measure_Field})*sum({Measure_Field2}) / (sum({Measure_Field2})^2),[])

I tried and it gives me the same value for every field down the column, which doesn’t match with what the excel formula generates.

Do you want to partition it by something?

avgOver(sum({Measure_Field})*sum({Measure_Field2}) / (sum({Measure_Field2})^2)

I would like to partition by month using [truncDate("MM",{reporting_period}) ASC]
Also, I am getting an error message that says “nesting of aggregate functions is not allowed”.

What are your measure fields?

Are they aggregations?

Yes, they are both aggregations.

Yes, so like it says you can’t do that.

Can you then try wrapping your {Measure_Field} in a sumOver instead of sum.

avgOver(sumOver({Measure_Field},[])*sumOver({Measure_Field2},[]) / (sumOver({Measure_Field2},[])^2)

You might need to add more partitions to each of these as well as work with the avgOver.