Calculations across semi-related fact tables

Using the AdventureWorks database as an example:

If I create a calculated measure that is the sum of total sales across InternetSales and ResellerSales, how do I ensure that when I am browsing the cube, that my total sales value is always accurate even when I filter the cube on a dimension that applies to one fact table, but not the other.

For example, if I am browsing the cube by Sales Territory and I apply a filter on a specific Reseller Type (for instance Warehouse), then I would expect that for Sales Territories that did not have ResellerSales with a Reseller Type of Warehouse, the value displayed for Total Sales would be blank.

However, because the non-empty behavior for the calculation is the combination of InternetSales and Reseller Sales, the InternetSales value will always be displayed (even though it does not make sense based on the filter).

Is there a way to change this behavior?

I ran the following query against an unmodified version of the Adventure Works DW database:

Code Snippet

with member [Measures].[x] as

[Measures].[Reseller Sales Amount] +

[Measures].[Internet Sales Amount]

select

{

[Measures].[Reseller Sales Amount],

[Measures].[Internet Sales Amount],

[Measures].[x]

} on 0,

Geography.Country.Members on 1

from [Adventure Works];

At the All Geographies member of the Country attribute hierarchy, Internet Sales Amount had a value of $29M. Against individual countries, this same $29M figure was repeated as the Internet Sales measure group has no relationship with this dimension. If you think about how SSAS handles cube space definition, this makes sense even though it is counter-intuitive to most users.

Then, the question is how is the calculated member [x] handled? The $29M is added to whatever value for Reseller Sales Amount is returned.

I then went into the cube designer in BIDS for the Adventure Works cube, selected the Internet Sales measure group, and then pulled up its properties. I set IgnoreUnrelatedDimensions to False and reprocessed the cube. When I executed the query again, I got the same $29M for the All Geographies member, but NULL for its children. The calculated member [x] reflected $29M + Reseller Sales Amount for the All Geographies member and just Reseller Sales Amount for the other members.

Bryan