I need to calculate sales commission for each region each year in a pivot table (whereby year and region are the dimensions).
There is a predefined commission fee ratio for each region each year and the commission fee is calculated as net sales * commission fee ratio.
For FY 13 I have some missing values in the field commission fee ratio so we'd like to estimate the commission fee for FY13 with the ratios of FY14.
My question is, how should I write the expression in the pivot table so that I can use the ratio of FY14 to calculate the commission fee for FY13? Or this kind of calculation must be done in scripting? (there are 4 columns in total in the raw table, Year, Region, NetSales and CommissionFeeRatio).
