Create Percentage Calculated Measure... Getting the current Dimension

Hello,

I created a cube for surveys and would like to create a measure for the percentage of people (surveys) that answered Q1, Q2, Q3, etc. I have the following calculated member:

CREATE MEMBER CURRENTCUBE.[MEASURES].[Percentage Answered Q2]

AS Case

// Test to avoid division by zero.

When IsEmpty

(

[Measures].[Surveys Count]

)

Then Null

Else ( [Q2].[Units].CurrentMember,

[Measures].[Surveys Count])

/

(

// The Root function returns the (All) value for the target dimension.

Root (), [Measures].[Surveys Count])

End ,

FORMAT_STRING = "Percent",

VISIBLE = 1 ;

Do I have to specify the dimension in the script (like [Q2].[Units].CurrentMember) ? That means I have to create one percentage calculation member for each of the questions (dimensions). Is there a way to get the current dimension?

I hope I explain my question clearly, please let me know if I can explain further.

Thank you very much,

Sincerely,

Annie

There is no such concept as "the current dimension". Any given cell in a cube is always defined by a coordinate of all attributes of all cube dimensions. From your description, it seems like you created one dimension per survey question. Then a coordinate in your cube space would consist of members from all question dimensions. You might have multiple measure groups in the cube, in which case a cube dimension may or may not relate to a given measure group.

In any case, the CurrentMember component in the tuple expression in your example is redundant and can be safely removed.

|||

Thank you so much for the prompt reply.

As I'm fairly new to MDX. Do you mean change the script to:

-

CREATE MEMBER CURRENTCUBE.[MEASURES].[Percentage Answered]

AS Case

// Test to avoid division by zero.

When IsEmpty([Measures].[Surveys Count]) Then 0

Else [Measures].[Surveys Count] /(Root (),[Measures].[Surveys Count])

End,

FORMAT_STRING = "Percent",

VISIBLE = 1 ;

-

This then gives an "#value" error.... Could you be a bit more specific?

Thank you so much,

Sincerely,

Annie

|||

The Root() function without argument returns a tuple containing members from all attribute hierarchies, including the measures hierarchy. The [Measures].[Surveys Count] component in your tuple will cause a duplicate hierarchy error.