Calculating the running average in a pivot table

Question

I have a pivot table that has the following structure: Row fields are month (number 1 through 12) and values (revenue, sqft, rev_per_sqft which is a calculated field). I also show the Running Total in based on the month row.

Now, this works perfectly for revenue and sqft.

But it does not work for rev_per_sqft. The formula for the field is =revenue / sqft. It works on an individual monthly basis but when I do Running Total in it just sums up the monthly values instead of doing the average. I selected both sum as well as average and it doesn't make a difference.

Is there a way to show the average?

If this hasn't been completely clear yet, here is how my table looks like:

+-------+----------------------+---------+
| Monat |        Werte         | Totals  |
+-------+----------------------+---------+
|     1 | Revenue              | 651.32  |
|       | sqft                 | 52.3    |
|       | Rev per sqft         | 12.45   |
|       | Running Revenue      | 651.32  |
|       | Running sqft         | 52.3    |
|       | Running rev per sqft | 12.45   |
|     2 | Revenue              | 476.17  |
|       | sqft                 | 87.21   |
|       | Rev per sqft         | 5.46    |
|       | Running Revenue      | 1127.49 |
|       | Running sqft         | 139.51  |
|       | Running rev per sqft | 17.91   |
+-------+----------------------+---------+

As you can see, the value for the Running rev per sqft starts to get distorted by the second month and continues to do so.

DMM · Accepted Answer · 2017-08-20 14:43:17Z

1

The difficulty you have run into is that you would like to be able to define a calculated field as (Running Total of Revenue)/(Running Total of SqFt) and there is no obvious way of doing this in your pivot table. Arithmetically. as you have found, this gives very different results from the running total of your calculated field of Revenue/SqFt.

One approach is to recast your pivot table so that it contains the monthly sums of Revenue and SqFt as columns along with their associated running totals. You can then use this pivot table as the data source for a second pivot table. In this second pivot table you can now define two calculated fields: one based on the ratio of the monthly values and a second based on the ratio of the running total values.

The picture below illustrates:

This is a slightly clunky solution as any changes to the source data will require "Change Data Source" and "Refresh" operations to be undertaken on both pivot tables in the correct order.

answered Aug 20, 2017 at 14:43

DMM

1,1477 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Spurious Over a year ago

This changes the entire readability of the table and distracts from the result. Thanks for the effort but this would destroy the usability of the table.

DMM Over a year ago

You can always put the intermediate pivot (and indeed the final pivot) on separate worksheets, if that's the readability issue with this solution. If there are usability criteria that you are also working to perhaps you should edit your question to make these clear.

Collectives™ on Stack Overflow

Calculating the running average in a pivot table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related