Dynamic Aggregated Average with Pivot Table

[gmlopez275]

I am trying to use pivot tables to display average Monthly Expenses for a set of products. I have not been able to find a workable solution on the internet nor this forum (although I have seen many with posts on the web with similar question).
I have incorporated a slicer (using Month grouping from transaction date). If I use the Average function on the pivot table, Excel divides Total Expense by the number of transactions. I want to see Total Expense/# of months.

The attached file shows in yellow the output I would like to see. One pivot table shows the monthly break down of expenses. I would like the other pivot table to shows Average Monthly Debit/Credit. A dynamic solution is preferable. If I hard-code formulas, it is not very elegant as observed when only two months are selected from the slicer.

Is this even possible? I saw a post with =Averagex(....) however, I could not get it to work.

MS Excel Office 2016, on Mac OS Sierra.
I have access to MS Office 2013 on a Windows OS if needed.

Any help much appreciated.

 

[Chihiro]

This sort of aggregation can't be done in regular pivottable without using intermediate calculation table (summarizing data by month) or you can do it with helper column(s). But do note helper column method can lead to misinterpretation of data by end users (especially when users can modify pivottable fields and/or expand to source data).

To use source table directly for this type of aggregation, you will need PowerPivot and need to add measure using DAX.

Edit: Added part about helper column.