MDX Calculated Member Help

[MAdvocate]

HI,

I am new to the world of MDX and any help would be greatly appreciated.

I am trying to write a calculated member to give me the sum of the lowest level of a hierarchy and then roll those numbers up as you drill up through the hierarchy. Also, I want the measure to only calculate the Pharmacy level's descendants.

Here is where I am.

SUM ( DESCENDANTS([Care Conn Catalog Synonym].[Catalog Type Hierarchy].[Catalog Type Desc].currentmember,[Care Conn Catalog Synonym].[Catalog Type Hierarchy].[Catalog Type Desc].&[Pharmacy]) ,[Measures].[CPOE Order Count])

As written I get a #Value Error.

Thank you in advance for your help!

[Island1]

Would you mind restating this using the Adventure Works cube? That will allow us to get you a working example as an answer.

Many thanks!

Bill

[MAdvocate]

Hi,

Thanks for the quick reply. No problem. Here is what I believe is the equivalent calculation using Adventure works.

SUM ( DESCENDANTS([Product].[Product Categories].[Category].currentmember,[Product].[Product Categories].[Category].&[1]) ,[Measures].[Order Count])

So I would like the calculation to add up all Bike Orders and then when I drill down to the Subcategory level to only show the order count for those members that belong to the bike category and to do the same for Product level if I were to drill down again. I hope that makes sense.

Thank you again for your help.