2

I am trying to TOTAL a measure with a calculated measure AS

CALCULATE;     
CREATE MEMBER CURRENTCUBE.[Measures].[Total On Hand Amount]
AS ([Warehouses].[Warehouses].[All],[Measures].[On Hand Amount]), 
FORMAT_STRING = "#,#", 
VISIBLE = 1;

This is working when I have warehouse Dimension selected but I want to make it dynamic so that it would work for any other dimensions/hierarchies as well without them being added to the code.

Any help is appreciated!!

1 Answers1

0

I think you want to use Root

CALCULATE;     
CREATE MEMBER CURRENTCUBE.[Measures].[Total On Hand Amount]
AS AGGREGATE(Root(),[Measures].[On Hand Amount]), 
FORMAT_STRING = "#,#", 
VISIBLE = 1;

Here is a small example with: Total Sales Amount

ROOT to get the value on the ALL level/default member for all dimensions

mxix
  • 3,539
  • 1
  • 16
  • 23
  • This code did not work for me. It would just return NULL values for [Total On Hand Amount] for any dimension. Am I missing anything else on this code? – Anup Pudasaini Nov 05 '18 at 05:31
  • Thank you for your help. I would consider this next time but I got a working code now here - https://stackoverflow.com/questions/53108651/calculated-members-with-multiple-attribute-hierarchy-mdx – Anup Pudasaini Nov 07 '18 at 16:06
  • But this does answer your question correctly? You even use Root on your other Duplicated question like I suggested here... Please accept this as an answer so at least it may help others. – mxix Nov 07 '18 at 16:52