total restricted values

[#1]

hi,

i have a straight table, and i use "dimension limits" to display only the top X items in the data using to the first expression in the table (sum(sales)). X is determined by the user.

what i'm trying to do now is to add a "Totals" row for the table, but i need it to sum only the top X items, as opposed to all the items in the data. any ideas?

thanks

[Nicole-Smith]

Instead of using dimension limits, you can use set analysis in your expression.  So an example (if your X was 2):

sum({<Customer={'=rank(sum(Sales))<=2'}>}Sales)

[MK_QSL]

Like this?

[aveeeeeee7en]

Hi Danny

Use below expression:

For Top 5 Sales:

=if(aggr(rank(sum(Sales)),Sales)<=5,Sales)

Regards

Aviral Nag

[#5]

hi, i must be doing something wrong. in the table i have [SKU] as a single dimension. i need to display the largest 10 values, using sum([Net Sales] as the expression for the ranking. then, i need to add a total row to sum only these 10 values. i tried to use your advice but for some reason it doesn't work. please see below the expression i used:

sum({<=[SKU]={'=rank(sum([Net Sales]))<=10'}>}[Net Sales])

[Nicole-Smith · Accepted solution]

You have an extra = in your set analysis.  It should look like this:

sum({<[SKU]={'=rank(sum([Net Sales]))<=10'}>}[Net Sales])