How to sum a calculated measure?

Question

I have a fairly complex calculated measure that works perfectly for each row of data from a cube. However, I then need a sum of those values line by line. But the behavior of calculated measures seems to be in the subtotal and total lines in Excel, it's performing the calculation again instead of summing the previous rows. Is there a way to have a calculated measure that performs it's calculation on each row, but they does a traditional SUM in the total and subtotals?

Thanks in advance.

Answer 1

A calculated measures does not aggregate; it will be computed each time.

Answer 2

There is a solution, but that is really ugly:

You would have to use something like

SCOPE([dim1].[hier1].[All]);
    [Measures].[MyCalculatedMeasure] = Sum([dim1].[hier1].[bottomlevel].Members, [Measures].[MyCalculatedMeasure]);
END SCOPE;

for all hierarchies of all dimensions, be it an attribute or user hierarchy, replacing dim1, hier1, bottomlevel as appropriate.

Answer 3

You need to immitate real measure behavior for calculated member. Make dummy real measure with Null value and then use Scope to define your formula on the granullarity level. For more detailed answer see here: http://blog.crossjoin.co.uk/2013/05/29/aggregating-the-result-of-an-mdx-calculation-using-scoped-assignments/

Answer 4

I found the easiest way to do this:

1. Create a new named calculation in the appropriate measure table in your DSV. (Right click, add new named calculation and set its expression to be CAST(NULL as [insert appropriate data type]).
2. Add the new named calculation as a measure in your cube.
3. In the calculations area of your cube switch to script view and add the following:

({[Measures].[The named calculation you created in step 1]},Leaves())=Your complex calculation;

This sets the definition at the leaf level and all the aggregations work perfectly.

Ref: http://sqlblog.com/blogs/mosha/archive/2005/02/13/performance-of-aggregating-data-from-lower-levels-in-mdx.aspx