locked
Averaging down a hierarchy RRS feed

  • Question

  • I have a requirement to perform a calculation by averaging down a hierarchy.  Currently this calculation is defined as shown below:

    MEMBER ParentAverage AS

    IIF( ISLEAF([MyDimension].[MyHierarchy].CURRENTMEMBER),

             [Measures].[ChildAverage],

             AVG([MyDimension].[MyHierarchy].CURRENTMEMBER.CHILDREN,

                  [Measures].[ParentAverage] )

           )

    This calculation gives me the expected results but I have started running into performance issues with it.  Given that this hierarchy only ever has two levels (i.e. parent, child) is there a better way of doing the same calculation?

    Friday, June 10, 2016 8:46 AM

Answers

  • Hi jonesri,

    In this scenario, when using ISLEAF() function, it will check the if current member contains descendants members for each member on rows which may consume performance. Since you only have two levels in your hierarchy, you can consider using Ordinal function in IIF condition.

    WITH MEMBER ParentAverage AS
    
    IIF( [MyDimension].[MyHierarchy].CURRENTMEMBER.LEVEL.ORDINAL=2,
    
             [Measures].[ChildAverage],
    
             AVG([MyDimension].[MyHierarchy].CURRENTMEMBER.CHILDREN,
    
                  [Measures].[ParentAverage] )
    
           )
    

    Regards,


    Simon Hou
    TechNet Community Support


    Monday, June 13, 2016 9:04 AM