CODEWITHSUNDEEP
excelpivot-table
Tart
Tart

Reputation: 305

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

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!

Upvotes: 1

Views: 8155

Answers (1)

jeffreyweir
jeffreyweir

Reputation: 4824

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.

Upvotes: 2

Related Questions