Calculated member formula for All member is not SUM of children

  • cliffb

    SSCarpal Tunnel

    Points: 4547

    Hello everyone,

    I am working on a proof of concept to move some heavy analytical calculations into SSAS. Everything was working fine until I hit a particularly complex calculation. What appears to be occuring is the children are SUMMED correctly, but the All member is not.

    Psuedo code

    MEMBER Measures.x AS

    SUM(

    [Dimension].[Attribute]

    ,

    CASE

    WHEN [Measures].[Cost] > 0 AND ([Measures].[Measure1]*[Measures].[Measure2]*[Measures].[Measure3])/[Measures].[Cost] > 5 THEN

    [Measures].[Cost]/([Measures].Measure1*[Measures].[Measure3])

    ELSE [Measures].[Measure2]

    END)

    I understand why it is happening, It's taking the values of the All member for the measures vs summing the result of the children. The question is how do I get the sum of the CHILDREN for All vs the calculation of the values of the All member?

    FYI, I have tried adding a Calculation in the cube and processed it, but I got the exact same result.

    Any help is appreciated.

  • cliffb

    SSCarpal Tunnel

    Points: 4547

    Nevermind, I found what I needed.

    For the Set argument, I used a case statement.

    SUM(

    CASE [Dimension].[Attribute].CURRENTMEMBER

    WHEN [Dimension].[Attribute].[All] THEN [Dimension].[Attribute].CURRENTMEMBER.CHILDREN

    ELSE [Dimension].[Attribute].CURRENTMEMBER

    END,

    ...

    )

  • write2mmk14

    Grasshopper

    Points: 23

    Thanks for posting the solution.It was a life saver! I had a similar problem and being an MDX newbie, was breaking my head to find the solution..

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply