Search code examples
sql-server-2012ssasmdxssas-2012

ssas mdx line quantity divided by SUM

How do I create the new column in MDX? The dimensions selected are not important. I just want the line amount / sum for the data selected. I am not an mdx expert...

enter image description here

enter image description here

Current code below :

CREATE MEMBER CURRENTCUBE.[Measures].[Weight]
 AS IIF([Measures].[Sales Line Amount AC Budget hidden] <> 0,
Divide([Measures].[Sales Line Amount AC Budget hidden]
 ,AGGREGATE(Root(),[Measures].[Sales Line Amount AC Budget hidden])),NULL), 
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'Sales Forecast'  ;

Hi they are the same column... I just reference a hidden column so i can change the properties on it not affecting the original measure. Are you sure the MDX will give me the result I want? Could it be a formatting issue? the format of that measure is #,0.00

enter image description here

Solution

  • OK! I think i understand what you want try this. With Root you can get the All from all dimensions.

    Divide(
  [Measures].[Sales Line Amount AC Budget hidden]
 ,AGGREGATE(
   Root()
  ,[Measures].[Sales Line Amount AC Budget hidden]
  )
)

    EDIT:

    Don't think there is a fairly easy way to do that. Maybe have a look at Axis(1) instead of root.

    I did not try this in a cube only within a query... and it works..

    Divide(
  [Measures].[Sales Line Amount AC Budget hidden]
 ,AGGREGATE(
   Axis(1)
  ,[Measures].[Sales Line Amount AC Budget hidden]
  )
)

    EDIT2:

    Since you want this to work with Excel. There isn't a clear way to make it work without knowing exactly which dimensions you want to "Weight". Excel Works with Subcubes, that means, the Axis function actually return ALL the members from that axis.

    There isn't a way to know exactly which members are being actually shown in MDX. This should be a Client side calculation.

    You can actually do this very easly in EXCEL simply by selection the field going to OPTION and going to "Show Values As". With "% Grand Total" will give you exactly the percentage you are looking for.

    Excel - Show Values As