Sales per customer

[seancsn]

I have a table for Sales and a table for the number of customers per Town. The relationship is set up in PP via the Town in both tables.

I have created calculated fields for Total Sales and Total customers.

I have also created a calculated field for Sales per Customer using =Divide([Total Sales],[Total Customers]).

The problem I am having is that when I add the Sales per Customer field to a Power pivot table it seems to using the total customers to make the calculation. This is confirmed when I add the Total customers calculated field to the table.

Please could you let me know what I need to correct?

Thanks

Sean

 

[gebobs]

Could you put a sample on Dropbox and post a link here?

 

[scottsen]

My guess is that on Rows of your pivot, you put the Town from the Sales table. Grab the one from the lookup table (Customer Per Town I guess?) and put THAT on rows. See if that helps your cause.

 

[seancsn]

Thanks gebobs and scottsen. I have provided the dropbox link to my sample powerpivot workbook for you to see what I am missing. I have highlighted the problem column in red on the pivot table.

Sales sample

Thanks in advance

Sean

 

[seancsn]

Just worked out scottsens suggestion and it worked! I had taken the town from Sales instead of from the customer table. Thanks again.