none
Combining Measures from Separate Measure Groups in MDX Calculation

    Question

  • 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 Eric C Ness Wednesday, June 13, 2012 5:34 PM
    Wednesday, June 13, 2012 5:31 PM

Answers

  • 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 Eric C Ness Thursday, June 21, 2012 12:46 PM
    • Edited by Eric C Ness Thursday, June 21, 2012 4:51 PM typo
    Thursday, June 21, 2012 12:46 PM

All replies

  • 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 Eric C Ness Thursday, June 14, 2012 2:22 PM
    • Unmarked as answer by Eric C Ness Thursday, June 21, 2012 12:46 PM
    Thursday, June 14, 2012 2:21 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 Eric C Ness Thursday, June 21, 2012 12:46 PM
    • Edited by Eric C Ness Thursday, June 21, 2012 4:51 PM typo
    Thursday, June 21, 2012 12:46 PM