CODEWITHSUNDEEP
ssasmdxcumulative-sum
Carlos
Carlos

Reputation: 2513

Calculating cumulative sum in MDX

I need a MDX query which returns the top N percent of the rows based on a cumulative sum of a measure. The tricky part is that the set must be ordered based on a different measure than is used for the sum, so using the TopPercent function is not possible.

To clarify what I need, here's an example. I want to buy at least 1000 grams of meat in as few packages as possible. However, it is not just about the weight of the package, I want quality, so I want prioritize products that contain the highest percentage of meat. The available products are these:

|| Name      || Weight || PercentageOfMeat ||
| Product 1  | 500     |  20               |
| Product 2  | 250     |  60               |
| Product 3  | 1000    |  25               |
| Product 4  | 400     |  50               |
| Product 5  | 400     |  40               |

So, to get what I want, I would first sort descendingly by PercentageOfMeat to meet my first priority. Then I would sum the weights of the products cumulatively until I reach the 1000 gram limit. The result should be Product 2, Product 4 and Product 5. That's simple enough.

But is it possible to do this in MDX? TopPercent (or in the case of the example, TopSum) would otherwise be perfect, but it does not allow me to use different measures for sorting and summing. If I wanted just the products that weight the most, it would work. To quote the documentation, the function:

Sorts a set in descending order, and returns a set of tuples with the highest values whose cumulative total is equal to or greater than a specified percentage.

What I basically need is TopPercent function that does not sort the set, but as far as I know, there is none. So is it possible to do what I want or do I have to calculate this in server code?

Upvotes: 1

Views: 5349

Answers (1)

Marc Polizzi
Marc Polizzi

Reputation: 9375

How about combining an Order() first to get your products sorted according to their percentage of meat and then Filter() them according to their cumulative weight ?

In [Adventure Works] the following code is showing the most ordered mountain bikes whose cumulative sales amount is lower to a certain value: 

with 

   set [mbikes] as order( [Product].[Product Categories].[mountain bikes].children, [order count], BDESC ) 

select 

    { [order count], [sales amount] } on 0,

    filter( [mbikes], sum( subset( [mbikes], 0, [mbikes].currentOrdinal ), [sales amount] ) < (8 * 1000 * 1000)  ) on 1 

from [adventure works]

giving the following:

                          Order Count    Sales Amount
Mountain-200 Black, 38          743      $2,589,363.78
Mountain-200 Black, 42          671      $2,265,485.38
Mountain-200 Silver, 38         641      $2,160,981.60

Upvotes: 5

Related Questions