PivotTable: Get average over period instead over occurrence

Hello,

I am creating a financial overview Excel file and I'd like to know the average over a certain period.
Let's say I have 12 months and in one month there is a cost of €1000 occurring once, then I want to know the average over the total period (i.e. 1000/12).
This so to show the monthly averages.
I know, that for good reasons Excel calculates average over the amount of times it occurs (i.e. 1000/1).

Nevertheless I want to know the figure over the amount of periods.
The periods will change as I am using a pivot table with Timeline.

Besides showing it finally in a chart, I also want to be able to show the averages in textboxes that I use in my dashboard.
Each textbox refers to a certain cell for a value.

However I need to retrieve the values.
First I don't get the average over the selected period to work. Once I have that I can use some formula's to get the values also in the textboxes no problem.
I tried many different kinds of things to get these averages.
All of them didn't work and therefore I hope you can help me.

An example file is attached.

Feb Shareable Overview - Avg.xlsm

Thanks in advance!

Originally Posted by JeteMc

In the sample file would you want June's average for Belastingen to display -1.86 based on -19.56 + -2.71= -22.27 and -22.27/12=-1.855833333?

Thank you for your reply

The intent for me is to get an average for the period selected using the slicer. So if these costs are the only ones in a year and that year is selected then indeed divide by twelve (like I'm your calculation). I am making a dynamic dashboard for which I want to add averages per category for the selected period in the slicer.

Hope this clarifies.

Thank you. This inspired me.
What I did to resolve it is to create one pivot table (somewhat like yours) and a formula to calculate the amount of months selected.
Then create a second sheet and make a table with a couple of formulas to extract the totals of each category.
Lastly I converted this data to a pivot table and voila. It worked.

Probably not the nicest solution but it works!