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