Ignore Dimension in Pivot Table

[#1]

Hi guys,

i am struggling with following issue:

let's say i have raw data like:

Year	City	Spent
2014	frankfurt	1200
2014	munich	3000

with this data i know how much money was spent for each city

&

City	customerId
frankfurt	1
frankfurt	2
munich	5
munich	6
munich	7

with this data i know which customer is connected to which city.

What i want to achieve know in Pivot Table is :

Based on the count of customers grouped by Year and City i want to Calculate the Column "Spent"

My Problem is i need to ignore the dimension customerId for the calculation of "Spent". I only know how mauch was spent in each city but not how much was spent per customerId. So i want to count the CustomerId per city and then Spent devided through Count of CustomerIds per city

so let's say: munich spent 3000 and has 3 connected customerIds --> 3000/3 =  1000

That should be the result:

Year	City	customerId	Spent per customerId
2014	frankfurt	1	600
2014	frankfurt	2	600
2014	munich	5	1000
2014	munich	6	1000
2014	munich	7	1000

i tried a lot with aggr and rangesum functions but didn't get it work yet.

Hope somebody can help

[#2]

thank you all for yor fast replies