MDX - same grand total even when filtering

Question

I have a calculated member which brings the total Sales for all Shops. The formula I used is this:

SUM(([SHOP].[SHOP].Members), [Measures].[SALES])

So, even if I filter for one Shop, the above calculated member still brings me the total for all shops.

The problem is that if I filter for any other dimension (let's say Region), my calculated member will bring me the total ONLY for the selected Regions - which I know is the correct behaviour.

But how can I make a calculated member that brings me the total Sales for all the Shops regardless of any filter?

Answer 1

You will need to anticipate any possible slicers and then add them into the tuple in your measure:

SUM(
  (
    [SHOP].[(All)]
   ,[REGION].[(All)]
  )
, [Measures].[SALES]
)

Mocked up in AdvWrks I anticipated the addition of a time slicer by adding the member [(All)] into my custom measure's first argument:

WITH 
  MEMBER [Measures].[X] AS 
    Sum
    (
      (
        [Subcategory].[(All)]
       ,[Date].[Calendar].[(All)]
      )
     ,[Measures].[Internet Sales Amount]
    ) 
SELECT 
  {
    [Measures].[Internet Sales Amount]
   ,[Measures].[X]
  } ON 0
 ,[Subcategory].[Subcategory].MEMBERS ON 1
FROM [Adventure Works]
WHERE 
  [Date].[Calendar].[Calendar Year].&[2008];

Answer 2

You are getting total for all shops because you have [SHOP].[SHOP].Members in the formula.

This bit confuses me:

how can I make a calculated member that brings me the total Sales for all the Shops regardless of any filter?

You already have such member. If instead you wanted a member which gets you sales for the selected SHOP, what you really need is:

SUM([SHOP].[SHOP].CurrentMember, [Measures].[SALES])

EDIT

In retrospect, I possibly understand your conundrum. If you want this calculation to be unaffected by selection of any other filter, what you possibly need to do is to edit the member's definition to include the ALL member for every hierarchy in the cube. Something like

SUM(([SHOP].[SHOP].Members, [Region].[Region].Members),......), [Measures].[SALES])

Why this is required is because every incomplete tuple is first completed by the engine. It implicitly adds the [ALL] member from each hierarchy unless that hierarchy is present in the slicer too. But, if you explicitly add the [ALL] member, it would supersede the filter selection.