Forum FAQ: How do I force the [All] member to return the total of members?

Question

When I query cube in my Cube Browser, suppose that the results are:

Product MEASURES.A MEASURES.B

A 50 200

B 60 100

TOTAL 110 300

I created a calculated measure X: MEASURES.A/ MEASURES.B, then I got this:

Product MEASURES.A MEASURES.B X

A 50 200 0.25

B 60 100 0.6

TOTAL 110 300 0.36

 

However, what I what is:

Product MEASURES.A MEASURES.B X

A 50 200 0.25

B 60 100 0.6

TOTAL 110 300 0.85

Answer

We can achieve that with SCOPE statement as option 1, or modify the Calculated Measure X as option 2:

1) Using SCOPE statement to overwrite the value of [All] member.

A. Create a named calculation on the fact table just returns NULL values.

B. Create a measure on this column with AggregateFunction Sum.

C. Deploy/process the cube, and then switch to Calculations tab. Click button ‘Script View’, overwrite the value as following:

SCOPE([Measures].[X], [Product].[Product].[Product].MEMBERS);

THIS=[Measures].[A]/[Measures].[B];

END SCOPE;

1) Modify the calculated measure like below:

WITH MEMBER [Measures].[X] AS

SUM(EXISTING [Product].[Product].[Product].MEMBERS, [Measures].[A]/[Measures].[B])

SELECT {[Measures].[A], [Measures].[B], [Measures].[X]} ON 0,

[Product].[Product].MEMBERS ON 1

FROM [your cube];