SQLServerCentral » Data Warehousing » Analysis Services » Calculated member formula for All member is not SUM of childrenInstantForum 2016-2 FinalSQLServerCentralhttps://www.sqlservercentral.com/Forums/SQLServerCentralTue, 23 Jan 2018 13:19:59 GMT20Calculated member formula for All member is not SUM of childrenhttps://www.sqlservercentral.com/Forums/FindPost1014456.aspxHello 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.
Mon, 07 Apr 2014 18:53:11 GMTcliffbRE: Calculated member formula for All member is not SUM of childrenhttps://www.sqlservercentral.com/Forums/FindPost1559319.aspxThanks 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..Mon, 07 Apr 2014 18:53:11 GMTwrite2mmk14RE: Calculated member formula for All member is not SUM of childrenhttps://www.sqlservercentral.com/Forums/FindPost1014465.aspxNevermind, 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,
...
)
Tue, 02 Nov 2010 07:30:00 GMTcliffb