Calculated measure

I have multiple fact tables very much like the internet-sales store-sales scenario described in the analysis services tutorial:

http://msdn2.microsoft.com/en-us/library/ms166568(SQL.90).aspx

I've created a calculated measure for total revenue (= [Ad Revenue] + [Preprint Revenue]) This works fine when the fact tables share a dimension such as customer. There are, however, dimensions that are specific to each of the fact tables

For example, if I display revenue by customer in the browser, I correctly get the sum of ad revenue and preprint revenue for total revenue. If I display revenue by Ad Product (dimension specifc to ads and not preprints) the total revenue figure includes the ad revenue that is specifc to the Ad product and all preprint revenue.

That's potetnially confusing for end users. Is there a way to hide the calculation when certain dimesnsions are selected or to convert the preprint revenue to 0?

thanks

Peter

Hi Peter,

For the Measure Group which includes [Preprint Revenue], will setting the IgnoreUnrelatedDimensions property to False achieve the desired result?

http://msdn2.microsoft.com/en-us/library/ms365411.aspx

>>

Configuring Measure Group Properties

IgnoreUnrelatedDimensions

Determines whether unrelated dimensions are forced to their top level when members of dimensions that are unrelated to the measure group are included in a query. Default setting is True.

>>

|||

Thanks Deepak. That was the answer.

Peter