I am trying to build a dimension and a cube that contains a measure of Avg Family Size and a dimension that group different types of families into different categories.
The dimension is a parent-child type but the parent should be the AVG of all children instead of SUM.
I tried to use Custom Rollup feature in dimension (there are 2 places, one during the dimension setup in pop-up window, there is check box then to define with MDX or use a column with unitary operator.Another place is in dimension Properties after dimension is setup).I cannot get it to work no matter what I do in dimension with MDX.The cube display #ERR for each cell.
Also, on cube setup, measure setup has a property of Aggregation.It has SUM, MAX, MIN, COUNT, DISTINCT COUNT but just does not have AVG.
So far, the only way is to define a calc measure with such MDX:
IIF(IsLeaf([Test1].CurrentMemeber), [Measure].size, AVG([Test1].CurrentMember.Children, [Measuer].size) )
This is sort of awkward because it renders the original Measure useless.
Any ideas to do this directly in Dimension or in Mesaure? This is for AS 2000.
One of the standard way of doing an average in AS is to set up 2 measures, a SUM and a COUNT and then create a calculated measure for the average and set it up as the SUM / COUNT. If you don't need the original SUM and COUNT measures you can set their visible properties to false. This would create an average for a single measure.
If you had a lot of measures and they all have to be averaged you could try something like one the following which I think should work across all measures (but I have not tested it)
AVG([Test1].CurrentMember,1))
OR
SUM(descendants([Test1].CurrentMember,1))/COUNT(descendants([Test1].CurrentMember,1))
The descendants(<member>,1) will go down 1 level if there is one, otherwise it will return the member itself, which helps avoid the IIF(IsLeaf(...)) test. And not explicitly stating a measure *should* mean that the query will use the current measure context.
