Hi Everyone,

I have created a small Power Pivot table by importing data from an external source.

The power pivot table currently contains 3 columns (4th column Average Spending to be added). e.g;

Postcode Area---------Amount--------Unique Customers--------Average Spending
CR--------------------2,308.33--------------9--------------------256.48
B---------------------5,093.12-------------16--------------------318.32
AB--------------------3,456.98-------------10--------------------345.70

(Please disregard the hyphens, only used them for formatting)

The 3rd column Unique Customers is an added measure with the formula =COUNTROWS(DISTINCT(SourceData.CustomerName)) which shows the count of UNIQUE customers.

I use this pivot table to find the total amount spent by number of individual customer in each postcode area.

I now want to add a 4th column Average Spending which divides the Amount by Unique Customers to give me average amount spent by each customer in that postcode area.

So far what I have learned is that one cannot add a calculated field/column to a power pivot table based on OLAP data source.

My question; is there a workaround or is there any way I can add that 4th column?

Any help is appreciated!

Thanks & kind regards