i am struggling with following issue:
let's say i have raw data like:
with this data i know how much money was spent for each city
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|
i tried a lot with aggr and rangesum functions but didn't get it work yet.
Hope somebody can help
Solved! Go to Solution.
thank you all for your fast answers.
your solutions are all in Straight Table.
But i need to show this in Pivot Table.
The problem with pivot Table is that i get wrong Totals!
Like when the City is not expanded the result should be 3000 for munich and when its expanded to customerId the result should be 1000 for each customer
try this in a pivot please:
If (Dimensionality() = 1,Sum(Spent), If (Dimensionality()= 2, Sum(Spent),sum(total<Year,City>Spent)/count(total<Year,City>CustomerId)))
HTH - Andy