SSAS How to scope a calculated member measure to a number of specific members?

Question

I'm trying to create a calculated member measure for a subset of a group of locations. All other members should be null. I can limit the scope but then in the client (excel in this case) the measure does not present the grand total ([Group].[Group].[All]).

CREATE MEMBER CURRENTCUBE.[Measures].[Calculated Measure]
 AS (
     Null
    ),
FORMAT_STRING = "$#,##0.00;-$#,##0.00", 
NON_EMPTY_BEHAVIOR = { [Measures].[Places] } 
,VISIBLE = 1 ,    ASSOCIATED_MEASURE_GROUP = 'Locations';

-----------------------------------------------------------------------------------------
 SCOPE ({([Group].[Group].&[location 1]),
        ([Group].[Group].&[location 2]),  
        ([Group].[Group].&[location 3]),
        ([Group].[Group].&[location 4]),
        ([Group].[Group].&[location 5])
        }, [Measures].[Calculated Measure]);

// Location Calculations 

THIS = (
      [Measures].[Adjusted Dollars] - [Measures].[Adjusted Dollars by Component] + [Measures].[Adjusted OS Dollars]
    ); 

END SCOPE;

It's as though the [Group].[Group].[All] member is outside of the scope so it won't aggregate the rest of the members. Any help would be appreciated.

Answer 1

Your calculation is applied after all calculations already happened. You can get around this by adding Root([Time]) to the scope, assuming your time dimension is named [Time]. And if you want to aggregate across more dimensions, you would have to add them all to the SCOPE.

In most cases when you have a calculation that you want too do before aggregation it is more easy to define the calculation e. g. in the DSV, e. g. with an expression like

CASE WHEN group_location in(1, 2, 3, 4) THEN
          Adjusted_dollars - adjusted_dollars_by_comp + adjusted_os_dollars
     ELSE NULL
END

and just make a standard aggregatable measure from it.

Answer 2

I've searched high and low for this answer. After reading the above suggestion, I came up with this:

1. Calculate the measure in a column in the source view table
   (isnull(a,0) - isnull(b,0)) + isnull(c,0) = x
2. Add the unfiltered calculated column (x) to the dsv
3. Create a named calculation in the dsv that uses a case statement to filter the original calc measure
   CASE WHEN location IN ( 1,2,3)
THEN x
ELSE NULL
END
4. Add the named calculation as measure

I choose to do it this way to capture the measure unfiltered first then, if another filter needs to be added or one needs to be taken off, I can do so without messing with the views again. I just add the new member to filter by to my named calculation case statement. I tried to insert the calculation directly into a named calculation in the dsv that filtered it as well but the calculation produced the incorrect results.