SQL Server Performance Forum – Threads Archive

# Calculating the total sum across different dim

Dear All, I have a cube with Four Dimensions .Location

Product

Time With measures

DispKgs

Ord Kg#%92s

Lines disp

Lines ordered

AIP IN KGS Calculated Measures

Value fill %

VALUE FILL Numerator

Ordered Kg Reason,

Ordered KG Location

Ordered KG Product And

Value Loss % This is the calculated measure where I am having an issue

Hence Instead of having one single Calculated measure I have made three calculated measures which are Locn.Loss % and Prdt.Loss and Rsn .Loss. The reason is this.

The Rsn Loss is given by the formula (([Measures].[Ord Kgs]*[Measures].[Aip In Kgs])-[Measures].[Value Fill Numerator])/([Measures].[Ordered KG Reason]*[Measures].[Aip In Kgs]) Where Ordered KG Reason sum (descendants(Reason.members),[Measures].[Ord Kgs])/6

I have divided it by 6 to adjust the values of Ordered KG Reason for Demo purposes. The Ordered Kg should be the total sum of Ord KGS for the dimension which is on the row field ( In this example Reason is on the row field) on an html page which uses pivot table (OWC Component) . The bottom line being that I want to calculate the sum of Ordered Kg#%92s for whichever dimension is brought on to the row field and replace Prdt Loss ,Reason Loss and Location Loss by a single measure called value loss which depends on the sum of ordered Kg#%92s of the row dimension . which will be possible if I can have just one ordered KG Measure instead of Ordered KG for every dimension. Lets say instead of reason dimension there was Location dimension the Ordered Kg Measure should be ale to calculate the sum of ordered kg measure for the location dimension. I,e the ordered kg measure should not have Dimension name hard coded in the formula like the one I have calculated. sum (descendants(Reason.members),[Measures].[Ord Kgs])/6 Hope you can help me with this formula Thanks in advance for your help. Hemanth

]]>