custom rollups in Analysis Server

[hm40]

Hello,
I am in the process of defining custom rollups for a time dimension. The time dimension has the members Year and Month.
I also have two measures, turnover, and fulltime.
For Year, I want to define an Average over the fulltime measure instead of a Sum (which is the default way Analysis Server handles the cube).
I checked books online; they show the process, but after adding a new column in which to store the the custom formulae, I cannot edit the 'Custom rollup Formula'
Anybody experience with this? Any other way to create this custom rollup? How to go about?

regards,

Hans

 

[ram1975]

If i understood your probelm i think you want to find an average of a particular measure instead of sum. There is a work around for this.

For example you want to find the average of Sales Measure. DO the following
1. Add Sales measure by name "Sales_Sum" with Aggerage
function set to "Sum"
2. Adda again sales measure by name "Sales_Count" with
aggregate function set to "Count"
3. Add a calculated measure by name "Avg" whose formula
should be Sales_Sum * Sales_Count.

I hope this would solve your problem.

 

[MDXer]

Depending on what you fact rows look like, you may want to use a distinct count instead of a Count. if you use a distinct count beasure to place it in a seperate cube that is a duplicate of the initial cube (same dimensionality) but containing only the Distinct count measure. Bring these 2 cubes together in a virtual cube.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck