Calculate average based on a value column (count) in a pivot table

1
votes

I'm looking a way to add an extra column in a pivot table that that averages the sum of the count for the months ("Count of records" column) within a time period that is selected (currently 2016 - one month, 2017 - full year, 2018 - 5 month). Every month would have the same number based on the year average, needs to be dynamically changing when selecting different period: full year or for example 4 months. I need the column within the pivot table, so it could be used for a future pivot chart. I can't simply use average as all my records appear only once and I use Count to aggregate those numbers ("Count of records" column).

My current data looks like this:

enter image description here

The final result should look like this:

enter image description here

I assume that it somehow can be done with the help of "calculated filed" option but I couldn't make it work now. Greatly appreciate any help!

1
excelpivot-table
Currently each year on your example has a subtotal for each year. Are you looking for a field just like that, except an average instead of sum, correct? – ashleedawg
I don't really need subtotals.. I'm trying to add a column that averages the sum of the count for the months within a time period (currently it's a year). I cannot use standard average option in pivot table as the average that I am looking for is dependent upon the sum of count. In ideal state, each month within a time period (currently it's a year) will have the same average number and it will be dynamically changing if you slice by different period (year or months, if you filter for example only for Jan-Apr, 2017). – Art
What version of Excel do you have? – jeffreyweir
Microsoft Excel 2013 – Art
Sorry for delay responding...been traveling. Just so I'm clear on what it is you want, can you amend your sceenshot so that it includes the numbers you're after? (Just populate them manually). Also, is the underlying date field grouped in the PivotTable by month and year? Or do all your dates in the raw data correspond to the first of the month? – jeffreyweir

1 Answers

2
votes

Using the DataModel (built in to Excel 2013 and later) you can write really cool formulas inside PivotTables called Measures that can do this kind of thing. Take the example below:

enter image description here

As you can see, the Cust Count & Average field gives a count of transactions by month but also gives the average of those monthly readings for the subtotal lines (i.e. the 2017 Total and 2018 Total lines) using the below DAX formula:

=AVERAGEX(SUMMARIZE(Table1,[Customer (Month)],"x",COUNTA(Table1[Customer])),[x])

That just says "Summarize this table by count of the customer field by month, call the resulting summarization field 'x', and then give me the average of that field x".

Because DAX measures are executed within the context of the PivotTable, you get the count that you want for months, and you get the average that you want for the yearly subtotals.

Hard to explain, but demonstrates that DAX can certainly do this for you.

See my answer at the following link for an example of how to add data to the DataModel and how to subsequently write measures:

Using the Excel SMALL function with filtering criteria AND ignoring zeros

I also recommend grabbing yourself a book called Supercharge Excel when you learn to write DAX by Matt Allington, and perhaps even taking his awesome online course, because it covers this kind of thing very well, and will save you significant head-scratching compared to going it alone.