I have a key figure margin % which is calculated in the update rules and stored in the cube at sales office, customer & 0prodh2 level.
I have a query which has sales office in the rows and margin % in the columns. After executing the query i have the margin % being aggregated at the sales office level. What i am interested is showing the average of margin % at any drill down level.
Sales office Customer Prod Hier Margin %
a x 10
a y 10
b x 10
b y 10
if i drill down to this level i am having correct value. But if i drill down to sales office i am getting 40 % what i am interested is 10%
if i drill down to sales office & customer level I am getting 20% for customer a and 20% for customer b. I want the value to be 10%
I tried to use the average of all values aggregation but did not work.