0

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.

Spurious
  • 1,903
  • 5
  • 27
  • 53

1 Answers1

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:

Pivot table using another pivot table as a data source

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.

DMM
  • 1,090
  • 7
  • 8
  • 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. – Spurious Aug 20 '17 at 16:18
  • 1
    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. – DMM Aug 20 '17 at 17:02