CODEWITHSUNDEEP
mdxolap
Travis
Travis

Reputation: 705

In MDX, how can I perform a calculation on the child members and THEN aggregate those children?

I have data like the table below, where Expected Shipment Amount New is effectively the average shipment amount per claim count, but I can't get that to aggregate correctly (it should be 26.3 total, not 0). Does anyone have any idea how this could be done?

CliID | Claim Count | Expected Shipment Amount | CliId Count   Expected Shipment Amount New
All   |      5      |           61.8           |       2     |              0
159061|   (null)    |          (null)          |    (null)   |            (null)
159063|   (null)    |          (null)          |    (null)   |            (null)
166759|      2      |           34.2           |      1      |             17.1
166769|   (null)    |          (null)          |    (null)   |            (null)
223983|      3      |           27.6           |      1      |             9.2

This pre member is supposed to calculate the average for each individual CliID, and return null if there is no Expected Shipment Amount:

CREATE MEMBER CURRENTCUBE.[Measures].[Expected Shipment Amount Pre] AS IIf(IsLeaf([Claim].[CliID].currentmember), ([Measures].[Expected Shipment Amount]/[Measures].[Claim Count]),0), VISIBLE = 0;

And then this is what should be visible so that everything is aggregated correctly:

CREATE MEMBER CURRENTCUBE.[Measures].[Expected Shipment Amount New] AS SUM([Measures].[Expected Shipment Amount Pre]), VISIBLE=1;

It's pretty clear that at the aggregate level it is looking at Expected Shipment Amount Pre and returning 0 since the aggregate itself isn't a leaf, but for the aggregate I'd like it to calculate all the children and then add them up. How can I achieve that?

Upvotes: 3

Views: 2484

Answers (1)

FrankPl
FrankPl

Reputation: 13315

In your second step, you should use

CREATE MEMBER CURRENTCUBE.[Measures].[Expected Shipment Amount New]
 AS SUM(EXISTING [Claim].[CliID].[CliID].Members, [Measures].[Expected Shipment Amount Pre]),
VISIBLE=1;

i. e. sum across the members of the members of the CliID level.

EDIT Something you could try to speed this up is to rely more on the built in Analysis Services aggregation. To do this, create a physical measure Expected Shipment Amount New based on the Expected Shipment Amount column in the fact table, and set its aggregation function to ``sum`. Then, in the calculation script, just add

SCOPE([Claim].[CliID].[CliID].Members);
    [Measures].[Expected Shipment Amount New] = [Measures].[Expected Shipment Amount] / [Measures].[Claim Count];
END SCOPE;

This will overwrite the calculation of 'Expected Shipment Amount New' on leaf level with the average calculation, but not state anything about the All level of the CliID attribute hierarchy. Hence, for this level, the specified aggregation of the measure, which is sum, should take place.

Upvotes: 2

Related Questions