Calculating the total sum across different dim | SQL Server Performance Forums

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
]]>