# 6-Month Average from a calculated field with filtering

Hey! I am trying to calculate a 6-month average for a calculated field that calculates the percentage of total. This table also filters out two columns, but the percent of total updates to always equal 100%. I am looking to find the 6-month average of this percentage contribution of total but I am getting nesting errors for the various formulas I have tried. Does anyone know how to solve this? I would then want to use this previous 6-month average to see how it deviates from the current month.

Hello @sofiserr !

If possible, could you share the calculation that youâ€™ve made that keeps running into errors? That might help narrow down where the issue is coming from.

At a glance from your post, I would recommend checking out the following if you havenâ€™t already:

1 Like

Hi Duncan. Thanks for getting back to me! My issue with periodToDateAvgOverTime is that I canâ€™t figure out how to select 6 months as my time frame. With windowAvg, it wants to calculate with the amount of rows above and below, but I want there to be the averages over the months and I have thousands of varying rows of data so I cannot count it. I was wondering if maybe you knew how to do a sort of dense rank function that would assign my ship months a number, and perhaps I could then use the windowavg function to the dense rank field.

Hey @sofiserr !

Could you try using a parameter with periodToDateAvg to set the end date as today as well as the period?

Something like this: `periodToDateAvg(Sales,{Order Date},MONTH,\${Previous6Months})`

Where Parameter set up looks like this:

Let me know if this works!

Hi Duncan. I am now getting this error:
Nesting of aggregate functions like {{aggregateFunction1}} and {{aggregateFunction2}} is not allowed.

The field that I am trying to average is a percent contribution to a total amount. Do you know how to fix this issue?

Hi @sofiserr,

To help look into this in more detail, would you be able to share an example of the data and the expected output that youâ€™re trying to achieve?

Also, are you able to share the calculated field that calculates the percentage of total?

Many Thanks,
Andrew

Here is a sample of the calculations that I am trying to do. I would like a contribution to total amount that is averaged.

On quicksight, I have a field that uses:
percentOfTotal
(
sum({bill}),
[{date}]
)

This is how I get the amounts that are seen in in the last pivot table. I then use quicksightâ€™s filtering function to get rid of the data that I do not want to contribute (in this example it is returns)

Hello @sofiserr - Since a few months have passed from your original post I am going to archive this question. If you still need assistance with this please post a new question in the QuickSight community and link to this topic. Due to the community focus on responding to questions with 0 replies, this will ensure you are a top priority for help from one of our QuickSight experts. Thank you!