Combining Measures from Separate Measure Groups in MDX Calculation

# Combining Measures from Separate Measure Groups in MDX Calculation

• Wednesday, June 13, 2012 5:31 PM

I'm working on a MDX calculation that I can't get right.  The calculation consists of multiplying measures from two different Measure Groups and then summing the results.  They are tied together by a date dimension.  There are also additional dimensions in the grain of FactTableA, but they don't participate in the calculation.  Here's a picture:

FactTableA has multiple records per KeyDate.  FactTableB has at most one record per KeyDate.  The MDX for the calculation is

```CREATE MEMBER CURRENTCUBE.[Measures].[Total]
AS
[Measures].[MeasureA] * [Measures].[MeasuresB],
VISIBLE = 1;     ```

The result that I want is

A1 * B1 + A2 * B2 + ...

So each record in FactTableA is correlated to the corresponding record on FactTableB by date.  What I'm getting after MeasureA and MeasureB are aggregated is:

(A1 + A2 + ...) * (B1 + B2 + ...)

Here's an example with some data:

FactTableA

KeyDate        KeyQuote    KeyLocation    MeasureA
20110901     1                  3                    100
20110901     2                  5                    80
20110910     3                  3                    200
20110925     4                  5                    160
20110925     5                  8                    250

FactTableB

KeyDate        MeasureB
20110901     0.50
20110910     0.55
20110925     0.61

What I'd like to get is:

100 * 0.50 + 80 * 0.50 + 200 * 0.55 + 160 * 0.61 + 250 * 0.61 = 413.05

But due to aggregation what I get is

(100 + 80 + 200 + 160 + 250) * (0.50 + 0.55 + 0.61) = 1311.40

Any ideas on what to do in the MDX calculation to make this work?

• Edited by Wednesday, June 13, 2012 5:33 PM
• Edited by Wednesday, June 13, 2012 5:34 PM
•

### All Replies

• Thursday, June 14, 2012 2:21 PM

I figured this out after a few more hours of grinding away.  The solution assumes that the date dimension has a user hierarchy set up.

The key is to create one measure with the totals for day-level only.  Another measure then adds these totals to get monthly and yearly totals.  This way you are avoiding aggregating MeasureB across dates.

First set up an empty measure, then use the SCOPE function to write the totals at the day level.  Don't write any totals for the [All] member or you'll run into the problem of aggregating MeasureB across days.

CREATE MEMBER CURRENTCUBE.[Measures].[TotalDay] AS NULL;

SCOPE(EXCEPT([Date].[Day].MEMBERS,[Date].[Day].[All]), [Measures].[TotalDay]);
THIS = [Measures].[MeasureA] * [Measures].[MeasureB];
NON_EMPTY_BEHAVIOR(THIS) = [Measures].[MeasureA];
END SCOPE;

Next set up the [Total] measure which simply sums the descendants of the current member of the date hierarchy.  This means the monthly sum will be the sum of all [TotalDay] measures for the month.  The same applies for year.

CREATE MEMBER CURRENTCUBE.[Measures].[Total] AS
SUM(DESCENDANTS([Date].[DateHierarchy].CurrentMember), [Measures].[TotalDay]);

This will return the expected results.  If anyone has any suggestions for improvements, please let me know.
• Marked As Answer by Thursday, June 14, 2012 2:22 PM
• Unmarked As Answer by Thursday, June 21, 2012 12:46 PM
•
• Thursday, June 21, 2012 12:46 PM

There is an even easier way to calculate this.  You have to specify the way to aggregate data at the day level using SUM, then higher levels of aggregation take care of themselves.  The new calculated member is

CREATE MEMBER CURRENTCUBE.[Measures].[TotalDay] AS

SUM(Descendants([Date].[Day].CurrentMember, [Date].[Day]),

[Measures].[MeasureA] * [Measures].[MeasureB],

NON_EMPTY_BEHAVIOR = { [Measures].[MeasureA] };

I got the idea for this from this blog post.

• Marked As Answer by Thursday, June 21, 2012 12:46 PM
• Edited by Thursday, June 21, 2012 4:51 PM typo
•