Top N Sum and Sum for Others

My data source is an AS-cube and I have a dataset like:

Category Value

A 10

B 10

C 10

D 10

E 10

F 5

When I create a table with a details group on "Category" with the following filter "Sum(Value), Top N,=5" then I see the expected categories : A until E. But the sum in the table footer shows 55 in stead of the expected 50.

I know that you can define a filter directly at the table level, but then I'm unable to filter a Top N on categories.

Is there a possibility to achieve this with a report (in stead of creating another MDX dataset with ranking and so on)

Regards Frank van Zuilen

In your footer where you compute the sum of all detail rows try to do something like this:

Sum(IFF([Value in top 5], Value, 0))

Not sure what would be the correct syntax of the above boolean expression [Value in top 5] but I would first start to research/experiment with this.

Hope it helps,

Dom.

Frank,

One option would be to set your MDX query to use the ORDER BDESC function over the catagory value. Then you could use a SUM(IIF(RowNumber(Nothing) < 5, Fields!Value.Value, 0).

Would that work for you

Jessica