CODEWITHSUNDEEP
ssasmdxdata-cube
Alex Saurus
Alex Saurus

Reputation: 49

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>

Upvotes: 1

Views: 1191

Answers (3)

halfer
halfer

Reputation: 20430

(Posted answer on behalf of the OP).

For some reason, I had to create a new named calculation in the .dsv that was literally just "1," and then divide the moving average of connectivity by the moving average of that named calculation. Now it is displaying the correct aggregation. (Just dividing by 1, or even the moving average of 1, does not work, there has to be two individual columns in the .dsv for it to work).

Upvotes: 0

whytheq
whytheq

Reputation: 35557

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.

Upvotes: 2

sandeep rawat
sandeep rawat

Reputation: 4957

SELECT Averages in Aggregations instead of sum in Right bottom corner present image posted in question.

for more deatil

https://support.office.com/en-us/article/Aggregations-in-Power-Pivot-f36a448a-4962-4baf-baa2-68187b6387ce

Upvotes: 0

Related Questions