· Jun 15, 2016

How to calculate a dimension percentage on a relationship cube using calculated members?

I am trying to calculate a dimension percentage for WFRole on a child cube named MbMRouteHistoryInitiatorObj joined to a parent cube named MbmQaSObj by using a calculated member dimension within MbmQaSObj. 

If I use the calculated member dimension value expression of [WFRole].[H1].[WFRole].CurrentMember/ [WFRole].[All WFRole].%ALL in the child cube MbMRouteHistoryInitiatorObj, the percentages work correctly.

I am not able to calculate the same percentage using the value expression [MbmRouteHistoryInitiatorObj].[WFRole].[H1].[WFRole].CurrentMember/ [MbmRouteHistoryInitiatorObj].[WFRole].[All WFRole].%ALLin the parent cube MbmQaSObj.  The %ALL gives an error.  Any ideas on how to make this work?

Discussion (6)1
Log in or sign up to continue

Conn and I reviewed the documentation page that goes over creating a calculated member for a percentage: _meas_recipes_percentages

We also looked into using creating a calculated member based on %MDX that would run a select on the child cube and display the results in a pivot table based on the parent cube.  For example, this %MDX based on the related cubes in the SAMPLES namespace does just that:

%MDX("WITH %PARM Group As 'value:' SELECT [AgeD].[H1].[Age Bucket].&[@GROUP] on 1 FROM [RelatedCubes/Patients]","Group",[RelatedCubes/Patients].[AgeD].[H1].[Age Bucket].CurrentMember.Properties("KEY")) / %MDX("SELECT FROM [RelatedCubes/Patients]")

However, rather than displaying data from two different cubes in a single pivot table using %MDX, we decided that the best option would be to create a dashboard that shows the same information but in multiple widgets.  Then we wouldn't need to worry about combining results from different cubes and the pivot tables would be easier to interpret.