Average among siblings, as a calculated measure, is typically used to compare the result of each member in a group where the group is formed from his siblings. In case of a known hierarchy, the core expression is relatively easy.
(
[<Dimension name>].[<Hierarchy name>].CurrentMember.Parent,
[Measures].[<Measure name>]
)
/
Count(
NonEmpty(
[<Dimension name>].[<Hierarchy name>].CurrentMember.Siblings,
[Measures].[<Measure name>]
)
)
<> represent placeholders.
The expression says – take the parent’s value and divide it by the count of siblings that have results (only those, not all of them). Of course, we should add testing for zero as well as testing for empty values. Also, we could use sum of siblings instead of parent’s value because at least one member has no parent (root member). Or we should test whether current member has a parent and if not, then only use the sum of siblings version of expression. Might be faster on large dimensions. For simplicity, we won’t cover it here, not to complicate too much.
iif(
IsEmpty( [Measures].[<Measure name>] ),
null,
iif(
Count(
NonEmpty(
[<Dimension name>].[<Hierarchy name>].CurrentMember.Siblings,
[Measures].[<Measure name>]
)
) = 0,
null,
Sum(
[<Dimension name>].[<Hierarchy name>].CurrentMember.Siblings,
[Measures].[<Measure name>]
)
/
Count(
NonEmpty(
[<Dimension name>].[<Hierarchy name>].CurrentMember.Siblings,
[Measures].[<Measure name>]
)
)
)
)
But if the requirement is to make that expression work for any hierarchy (no matter whether that dimension only or for any hierarchy in a cube), we have to reach for the Axis() function.
In case of Excel 2007 as a front-end, the following code should be put into MDX script. Only one thing will be dependant on your cube and that is the first calculated measure. There you should provide a measure for calculation of average among siblings. In this code, Sales Amount from Adventure Works 2008 was used, but as I said, any measure will do. The rest of the code is cube independent, meaning, it will work in any cube and for any hierarchy.
Create Member CurrentCube.[Measures].[Original Measure] AS
[Measures].[Sales Amount]
;
Create Member CurrentCube.[Measures].[Where are measures in Excel] AS
case
when NOT IsError(Extract( Axis(0), Measures ).Count) then 0
when NOT IsError(Extract( Axis(1), Measures ).Count) then 1
else -1
end
;
Create Member CurrentCube.[Measures].[Where are non-measure rows] AS
iif(
NOT IsError(Axis(1).Count) AND
[Measures].[Where are measures in Excel] < 1,
1,
iif(
[Measures].[Where are measures in Excel] <> 0 AND
NOT IsError(Axis(0).Count),
0,
null
)
)
;
Create Member CurrentCube.[Measures].[Average Measure] AS
iif( IsEmpty([Measures].[Original Measure]),
null,
iif( IsEmpty([Measures].[Where are non-measure rows]),
[Measures].[Original Measure],
iif( Axis([Measures].[Where are non-measure rows]).Item(0).Item(0)
.Hierarchy.CurrentMember.Level.Ordinal = 0,
[Measures].[Original Measure],
Sum(Axis([Measures].[Where are non-measure rows]).Item(0).Item(0)
.Hierarchy.CurrentMember.Siblings,
[Measures].[Original Measure])
/
Count(NonEmpty(Axis([Measures].[Where are non-measure rows]).Item(0).Item(0)
.Hierarchy.CurrentMember.Siblings,
[Measures].[Original Measure]))
)
)
)
, FORMAT_STRING = ‘Currency’
;
As you can see, we define a calculated measure Original measure to be used throughout the other part of the code. Next, we test where Excel 2007 has put the measures. They could be on rows, columns or slicer (WHERE part). Excel uses only 2 axes and a slicer, doesn’t use axis(2) aka pages like OWC.
We test by counting measures extracted from an axis and testing whether that produces an error. If so, measures are not there. If they are not on rows or columns, then they must be in slicer. When we have only one measure, Excel puts that measure in slicer. When there are more measures, they are either on rows or columns, depending on other hierarchies in query.
Once we know where the measures are, we test where is a hierarchy that we need an average for. Although it seems sometimes that hierarchy is on one particular axis (let say rows), Excel tricks us with display and generates MDX with that hierarchy on columns for example. Not always, but sometimes. That’s why we need to test where that hierarchy is. Btw, we’re interested in the first hierarchy that appears on rows, that is the mode of work. And we return the average among siblings result for its members.
The next calculation follows. If there is something on rows in query and measures are on columns or slicer, then we have the position of our hierarchy (it is positioned on rows). If the measures are not on columns but something else is (that’s the scenario where Excel swaps axes), then take position 0 as the place where our hierarchy is. As said, hierarchy is actually on columns although visually it looks like it’s on rows. In all other scenarios take null as result.
Finally, we come to the main expression. As explained in the simple example above, we must test whether the original measure was empty. If so, we must provide the same for our calculated measure not to include that row in result when NON EMPTY was used on axes. Then we test where that hierarchy of choice is. In case of null, which means there is no hierarchy in the query other than measures, we provide the original value since the average of a single member is the same member’s result. In the main part of this expression we test for root member and provide the same value (orig
inal value) since that’s an average for him. In all other cases the expression comes down to sum of siblings over count of siblings with result (hence NonEmpty() part). Without handling empty members, our average would be false.
In the end, we take care of format string and test if it works.
That’s it.
Here’s an example of it in action.
The article was inspired by this thread.