1

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>
Bhargav Rao
  • 50,140
  • 28
  • 121
  • 140
Alex Saurus
  • 49
  • 6

3 Answers3

2

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.

whytheq
  • 34,466
  • 65
  • 172
  • 267
  • Well, not entirely I guess. Would [OrderKey].CurrentMember work in that context? I'm amazed that for all my hours of Google searching, the "EXISTS" function did not come up in even one result. Thanks for your answer! – Alex Saurus May 09 '16 at 12:59
  • @AlexSaurus mdx is a bit of a secret language (also a dying one). Exist can be used in two different ways: let me edit the answer. – whytheq May 09 '16 at 15:22
0

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

sandeep rawat
  • 4,797
  • 1
  • 18
  • 36
0

(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).

halfer
  • 19,824
  • 17
  • 99
  • 186