none
MDX incorrectly summing result of multiplication of percentages RRS feed

  • Question

  • I am multiplying one set of values against a corresponding set of other values and wish to sum the result of all the products.  I am querying using MDX and when I get the results from the query and sum them in excel, they add up to the correct number.  However, when MDX sums them, the number is off.

    The calculation works as follows:

    I am multiplying a "Yearly Average Save Rate" percentage times a "Account Volume" percentage.   Both of the percentages are subdivided into buckets with a couple dimensions.  The multiplication is like the SUMPRODUCT function in excel, the save rate percentage in bucket A is multiplied by the Account Volume percentage in bucket A, and so on.

    If I look at the result of January 2018, I see the products of these multiplications are:


    • 7.71%
    • 17.88%
    • 0.60%
    • 22.22%
    • 15.14%
    • 0.47%

    When I sum these in excel, I get 64.02%.  When I use MDX, I get 63.83%.  Here is my MDX so far for creating some of the calculated members:

    WITH MEMBER [save rate times volume]
    AS
    (
        [Measures].[Non-fixed Principal Save Rate] * [Measures].[Target Setting Account Volume]
    ), 
    FORMAT_STRING = "PERCENT"
    
    MEMBER [Average yearly Save Rate]
    AS
    (
       Avg( 
             
               ([Measures].[Non-fixed Principal Save Rate], 
                [Date].[Calendar Month].[All])
       )         
    )
    
    MEMBER [volume times yearly save rate average]
    AS
    (
     [Measures].[Target Setting Account Volume]  *  [Average yearly Save Rate]
    )


    [volume times yearly save rate average] is the calculated measure that provides the values in my example above.  Anybody have any idea why MDX calculates the SUM of these percentages incorrectly?  or any advice on how to ensure it calculates correctly?


    • Edited by jjonathann Tuesday, November 19, 2019 3:14 PM clarifying why example values came from
    Tuesday, November 19, 2019 2:54 PM

Answers

  • This is most likely an order of operations issue. For measures like your [save rate times volume] MDX will always resolve the dependant measures first. So it will effectively do:

    SUM( [Measures].[Non-fixed Principal Save Rate] ) * SUM( [Measures].[Target Setting Account Volume] )

    it will not do

    SUM( [Measures].[Non-fixed Principal Save Rate] * [Measures].[Target Setting Account Volume] )

    If you want the second order of operations you are better to either create a calculated column in your view or DSV if the base columns are in the same measure group. Or to create a measure using a Measure Expression (if you are using the Enterprise Edition).

    If you can't use any of those approaches you can do it in MDX with an expression like the following in your cube script:

    CREATE MEMBER CurrentCube.[Measures].[save rate times volume]
    AS NULL;

    SCOPE (LEAVES());
      [Measures].[save rate times volume] = [Measures].[Non-fixed Principal Save Rate] * [Measures].[Target Setting Account Volume]
    END SCOPE;

    There would also be a way of doing this operation inline in a query, but it would depend on the grain of the query axis. I don't think there is a generic way of writing this at query scope.


    http://darren.gosbell.com - please mark correct answers

    Wednesday, November 20, 2019 3:06 AM
    Moderator