SOLVED

Calculate Monthly Average on a Pivot Table

Copper Contributor

Hi,

I have a table with all my daily expenses (that I extracted from my credit card reports). I made categories and subcategories for these expenses

I created a Pivot Table to group these expenses by year, month and so on...

But what I'd like is a way to get an average of these expenses per year and per month. I mean, for example, average of my Grocery Shopping in 2021. 

I tried to get the Average with the Average field of the Pivot Table but it doesn't work as expected:

For example, in the row "Amelia" and the subrow "Deporte y Gimnasio", for 2021, the average is -35. Probably because I have 3 itmes "Deporte y Gimnasio" in 2021 that sums up to -105. But what I would like is to get the monthly average, in that case 105/8 = 13,125 ... (divided by 8 because August is so far the 8th month of the year)... Of course, for 2020 that average would be, for the same row Amelia and category "Deporte y Gimnasio", 6/12 = 0,5.

 

My date fields are correctly formatted as date, and days/months/years are correctly recognized...

 

I hope I made it clear. 

 

Thanks in advance

Nicolas.

9 Replies

@nicolasdaudin 

 

When I do the same thing with my Pivot Table that tracks my checking accounts AND my credit cards, by budget categories, I use the Pivot Table to do the monthly totals and just create a separate column off to the side (NOT part of the Pivot Table but adjacent to it) and calculate the averages there. Perhaps that will work adequately for you.

 

 

@nicolasdaudin 

So, instead of SUM(Values) / COUNT(Values) you need as average

SUM(Values) / COUNT(months in the period)

That's easy to do with data model

image.png

but afraid it's still not available for Excel on Mac. 

So my solution (calculate average monthly off to the side) DOES work on the Mac, which is also the environment in which I work.

@mathetes 

Not sure it's the same. For example, you have only one $100 expense in May. When average for year 2021 will be 100/8; average for 1H2021 is 100/6; average for 2Q2021 is 100/3 and for other quarters is 0/3. Finally average for May is 100/1 and for other months is zero.

It all depends on how you're using the monthly averages for budget tracking and planning purposes. If, for example, I only have one expense for auto insurance--say $700 in May--but that covers the year's auto insurance, it still is legit to say that the average monthly expense is 700/12 and I might need to plan to accumulate cash at that rate to pay for next year's insurance.

As is often the case, not only does excel offer multiple ways to do the same thing; there are different meanings assigned to the same words: so what do we mean when we talk of "average monthly"? And those definitions of meaning affect the Excel method to resolve.
Hi guys, first thanks to both of you Sergei and Mathetes!
By "average monthly" i mean the total spent during the year divided by the number of months of the year (12 for a finished year, less than 12 if the year is not over). My idea is to compare categories and their spendings. I want a monthly average, not an expense average (I just realized that this is what I get when using the average thing in the pivot table, for example one expense of 6 euros => average is 6 euros. )
Hi Sergei thank you!
What do you mean by "with data model". You mean my raw data, and directly calculate there in the excel sheet instead of using the pivot table?
best response confirmed by nicolasdaudin (Copper Contributor)
Solution

@nicolasdaudin 

That's for Windows:

- creating PivotTable you have an option to add data to data model

image.png

- in data model you have an option to add measures

image.png

- for above sample measure could be

image.png

which we may use in PivotTable.

I attached the file with the sample, you may try to open it. I'm not on Mac, but as I know it doesn't support such functionality. Perhaps something changes recently.

thank you, I'm gonna try!
1 best response

Accepted Solutions
best response confirmed by nicolasdaudin (Copper Contributor)
Solution

@nicolasdaudin 

That's for Windows:

- creating PivotTable you have an option to add data to data model

image.png

- in data model you have an option to add measures

image.png

- for above sample measure could be

image.png

which we may use in PivotTable.

I attached the file with the sample, you may try to open it. I'm not on Mac, but as I know it doesn't support such functionality. Perhaps something changes recently.

View solution in original post