Search code examples
ssasmdxdata-cube

SSAS Data Cube, how can I get an average instead of a sum as a dimension aggregation?

I am working with a data cube that has a calculated member that is a percentage, and I need to get the aggregation for that member to be an average and not a sum.

Here is a screenshot or the cube in Excel:

enter image description here

The bolded percentages are a sum, which makes no sense for a percentage. How can I get them to be an average of the grouped percentages?

The Unit ID is one dimension, and the Order ID is a separate dimension. An order contains a set of unit IDs. I tried to create a separate calculated member for orders, but I do not know how to represent this set in MDX, though in SQL it would be very easy.

In other words, I want to represent the following SQL statement as a set in MDX: 

SELECT [UnitSMA] WHERE [OrderKey] = <OrderKey>
Solution

  • If the answer is really just to represent this SELECT [UnitSMA] WHERE [OrderKey] = <OrderKey> then the following is sufficient:

    EXISTS(
  [fact connectivity].[UnitSMA].MEMBERS 
 ,[fact connectivity].[OrderKey].[<OrderKey>]
)

    ...so I'm guessing that your "in other words" is not correct?

    EXISTS - MSDN here : https://msdn.microsoft.com/en-us/library/ms144936.aspx?f=255&MSPPError=-2147217396

    If you are looking for members from one hierarchy by using a member from a second hierarchy, and both hierarchies are part of the same dimension then the version of Exists above is ok.

    If the member is from a different dimension then you need to use the second form of Exists that includes a third argument - which is the name of a Measure Group that links the two dimensions.