excel - Calculating the running average in a pivot table -


i have pivot table has following structure: row fields month (number 1 through 12) , values (revenue, sqft, rev_per_sqft calculated field). show running total in based on month row.

now, works revenue , sqft.

but not work rev_per_sqft. formula field =revenue / sqft. works on individual monthly basis when running total in sums monthly values instead of doing average. selected both sum average , doesn't make difference.

is there way show average?

if hasn't been clear yet, here how 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 can see, value running rev per sqft starts distorted second month , continues so.

the difficulty have run able define calculated field (running total of revenue)/(running total of sqft) , there no obvious way of doing in pivot table. arithmetically. have found, gives different results running total of calculated field of revenue/sqft.

one approach recast pivot table contains monthly sums of revenue , sqft columns along associated running totals. can use pivot table data source second pivot table. in second pivot table can define 2 calculated fields: 1 based on ratio of monthly values , second based on ratio of running total values.

the picture below illustrates:

pivot table using pivot table data source

this clunky solution changes source data require "change data source" , "refresh" operations undertaken on both pivot tables in correct order.


Comments

Popular posts from this blog

ubuntu - PHP script to find files of certain extensions in a directory, returns populated array when run in browser, but empty array when run from terminal -

php - How can i create a user dashboard -

javascript - How to detect toggling of the fullscreen-toolbar in jQuery Mobile? -