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:
this clunky solution changes source data require "change data source" , "refresh" operations undertaken on both pivot tables in correct order.
Comments
Post a Comment