I would like to calculate the total business done for a week irrespective of the dimension used and I have created a member ([Measures].ForAWeekOfYear) for the same. The Percent1 member calculates the percentage of business done by a company.
with member [Measures].ForAWeekOfYear as
sum( [Dimdate].[Week Of Year] , [Measures].[TotalBusiness] )
member Measures.Percent1 as [Measures].[TotalBusiness]/[Measures].ForAWeekOfYear
select { [Measures].ForAWeekOfYear , ( Measures.Percent1 ) } on 0,
{ [Dim Customer].[Company].members * [Dimdate].[Week Of Year].Members} on 1
from [Account]
The problem I have if I use the above MDX expression is that the calculation gets split based on the Customer dimension and I assume that this is how the query should work. I assume I have to use the SCOPE statement, but not sure how to use it.
Please note that I am trying to execute this in Management console and not within a cube.
If you don't want your calculation to depend on Customer dimension, you should include Root(Customer). If you want to abstract from all of the dimensions in the cube - use Root().
HTH,
Mosha (http://www.mosha.com/msolap)
