Aggregate a custom Calculated Field for average

Question & Answer
,
1

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:

runningSum 
(
  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?

2

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

1 Like
3

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)

4

Have you tried taking the avgOver?

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

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.

6

Do you want to partition it by something?

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

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”.

8

What are your measure fields?

Are they aggregations?

9

Yes, they are both aggregations.

10

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)
,[{grouped_field}])

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