ssasmdxmultidimensional-cube

MDX Scope to get denominator

I have a SSAS multidimensional cube and i am trying to calculate a denominator for a query by using a scope statement.

I am trying to assign the measure "Total SalesCumulative" the total value of all the sales for all the stores. I want this to stay being the total value and not get sliced when in a pivot table. The measure called Total Sales sums up all the sales for each store. I have gotten as far as the below but this just returns the value for each store and shows the same value as Total Sales.

 SCOPE ([Measures].[Total SalesCumulative],[Dim Store].[Store Name].members);
 THIS = ([Dim Store].[Store Name].[All],[Measures].[Total Sales]);
 END SCOPE;

Has anyone got any suggestions how I can amend this?

Solution

  • I have checked your formula on Adventure Works database, and it seems correct. Just to be sure, you can check what you get with the query like the one below in SSMS (without making scope assignment): 

    with member measures.[All Order Count] as
([Measures].[Internet Order Count],[Product].[Model Name].[All Products] ) 

select {[Measures].[Internet Order Count] ,measures.[All Order Count] } on 0,
[Product].[Model Name].members on 1
from [Adventure Works]

    enter image description here

    1. Have you tried to clear cash before running the query after you made the change?

    2. Secondly, maybe there is some other scope assignment in the script, that affects the same measure and dimension and overrides your formula, because the last scope assignment wins.

    Try to add FREEZE(THIS) to your scope statement just to check if it will change the numbers:

    SCOPE ([Measures].[Internet Order Count],[Product].[Model Name].members);
 THIS = ([Product].[Model Name].[All Products] , [Measures].[Internet Order Count]) ;
FREEZE(THIS);
 END SCOPE;