Deepak Puri
This seems like a good scenario for "measure expressions" - if you inspect the Adventure Works cube, various Sales Amount measures are multiplied by currency rates. For example, you could create a new "sum" measure like [Value] on the MG1 "Units" field. Then, for this new measure, configure the "Measure Expression" property as:
[Measures].[Value] * [Measures].[Price]
This entry in Chris Webb's blog discusses measure expressions:
...
But here's a cool cube design feature that doesn't cause any conflict of interest for me - measure expessions. The easiest way to explain what they are is to explain one scenario where they're useful. In AS2K, if you've tried to model currency conversion, you're probably aware that you need to multiply the measure values in your main fact table by the currency rate before any aggregation of the resulting values takes place. You then have two choices for your cube: either precalculate the values in the fact table itself or in a view, which leads to much faster queries but also much bigger cubes, and which means the rates can't then be altered without reprocessing; or do the currency conversion at runtime using MDX, which generally leads to slower queries but which allows users to change the currency rates dynamically (for example using writeback). Neither of these options are exactly ideal so in AS2005 measure expressions offer a kind of halfway house - they are calculated at query time and yet are much faster than the equivalent MDX, but the price you pay is that they are nowhere near as flexible as calculated members in terms of the calculations you can define.
...