MDX calculation. Divide each member by grand total

Question

SAMPLE DATA

I have exported OLAP cube in following format (Measure [Some Percent] usage is Average over time):

             [Net Weight]   [Some Percent]
             4 387          2,10%
             3 304          1,60%
Grand total: 7 691          1,85% -- Percent is AVG

FORMULA

I need to create new calculated member [Modified Percent] which should be calculated in following:

[Net Weight].[1] / [Net Weight].[Total] * [Some Percent].[1] + 
[Net Weight].[2] / [Net Weight].[Total] * [Some Percent].[2] +
[Net Weight].[n] / [Net Weight].[Total] * [Some Percent].[n] -- can be n rows

FORMULA WITH SAMPLE DATA

So with my sample data will be:

4 387 / 7691 * 2,10 +   |   1,20%
3 304 / 7691 * 1,60     |   0,69%
                    =   |   1,89% -- Sum of percent

DESIRED OUTPUT

[Modified Percent] should be returned in following:

             [Net Weight]   [Some Percent]             [Modified Percent]
             4 387          2,10%                      1,20%
             3 304          1,60%                      0,69%
Grand total: 7 691          1,85% -- Percent is AVG    1,89%

MDX Script

For now I have MDX Script below, but [Modified Percent] returning the same values as [Some Percent]

CREATE MEMBER CURRENTCUBE.[Measures].[Modified Percent]
 AS ([Measures].[Net Weight] / sum([Vendor Invoice].[Vendor Invoice No].[All],[Measures].[Net Weight]))  * [Measures].[Some Percent], 
FORMAT_STRING = 'Percent', 
NON_EMPTY_BEHAVIOR = { [Net Weight] }, 
VISIBLE = 1;

Also tried this, but unlucky, the same result too:

CREATE MEMBER CURRENTCUBE.[Measures].[Modified Percent]
 AS ([Vendor Invoice].[Vendor Invoice No].CurrentMember,[Measures].[Net Weight]) / 
     iif(
        ([Vendor Invoice].[Vendor Invoice No].CurrentMember.Parent,[Measures].[Net Weight]) = 0,
        ([Vendor Invoice].[Vendor Invoice No].CurrentMember,[Measures].[Net Weight]),
        ([Vendor Invoice].[Vendor Invoice No].CurrentMember.Parent,[Measures].[Net Weight])
        )
      * [Measures].[Some Percent], 
FORMAT_STRING = 'Percent', 
NON_EMPTY_BEHAVIOR = { [Net Weight] }, 
VISIBLE = 1;

Looks like divide part returning 1. Have you any ideas how to solve It? If something is unclear - ask me, I will provide more details.

Your code looks okay to me. If instead of `create member`, you use `WITH member...`, is the result fine? — SouravA, Sep 08 '15 at 13:09
@SouravA maybe you're misinterpreting the question matey - I think this one is in connection with the "cube script" ... hence the use of `CREATE` — whytheq, Sep 09 '15 at 13:02
@whytheq No no. Actually I was wondering if Query Scoped members work fine or not. I have to test this one out in AW. — SouravA, Sep 09 '15 at 13:57

Sam Holloway · Accepted Answer · 2015-09-09T09:16:18.673

1

The problem is that the calculation is being applied at the total (All) level, where [Measures].[Net Weight] is equal to SUM([Vendor Invoice].[Vendor Invoice No].[All], [Measures].[Net Weight]) and hence the adjustment factor is 1.0

Try placing this whole block in the cube's MDX calculation script:

CREATE MEMBER CURRENTCUBE.[Measures].[Modified Percent]
     AS ([Measures].[Net Weight]
         / sum([Vendor Invoice].[Vendor Invoice No].[All], [Measures].[Net Weight]))
       * [Measures].[Some Percent], 
    FORMAT_STRING = 'Percent', 
    NON_EMPTY_BEHAVIOR = { [Net Weight] }, 
    VISIBLE = 1;  

SCOPE ([Measures].[Modified Percent], [Vendor Invoice].[Vendor Invoice No].[All]);
    this = SUM([Vendor Invoice].[Vendor Invoice No].[All].children, [Measures].[Modified Percent]));
END SCOPE;

This overrides the total and tells it to sum the children, rather than recalculating.

edited Sep 09 '15 at 09:16

answered Sep 08 '15 at 12:56

Sam Holloway

1,999
15
14

Wouldn't `[Measures].[Net Weight])` be same as `([Vendor Invoice].[Vendor Invoice No].CURRENTMEMBER, [Measures].[Net Weight])` – SouravA Sep 08 '15 at 14:25
Thank you for an answer, but there is something wrong with `SCOPE`. When I'm trying to add `[Modified Percent] ` to excel cube It starting `Running olap query` and freezes (waited for 2 hours), If I remove scope It adding normally, but with incorrect percents. – Stanislovas Kalašnikovas Sep 09 '15 at 06:04
Apologies, yes, there is a recursion in my original SCOPE statement. I have corrected it now. – Sam Holloway Sep 09 '15 at 09:15

MDX calculation. Divide each member by grand total

1 Answers1