7 Replies Latest reply on Mar 25, 2016 7:18 AM by Maged Asaad

# Subtotal for Top N within a category

In the attached file, how can I get the subtotal for the Top N Products by Category ? I used Index() , Nested Sorting and Top X parameter to get the Top N list.

* Note that I read couple of solutions to similar question but I couldn't follow up or apply to my data, so I used a very simple example in the attached file.

Thank you

• ###### 1. Re: Subtotal for Top N within a category

Hi, Maged

Change category filter to context filter, then you can get TOP N within that category.

See attached help.

Thanks,

Shin

• ###### 2. Re: Subtotal for Top N within a category

Sorry , I miss-understood the question.

Simply remove couple of rows.

Because you already filtered TOP N by different formula, simply add subtotal on simplest table.

Thanks,

Shin

• ###### 3. Re: Subtotal for Top N within a category

Hi Shinichiro,

The Subtotals in your solution is the Sum of Sales for all the products under each Category. Its not dynamic for the Top N .

• ###### 4. Re: Subtotal for Top N within a category

Oh, sorry.  Looks like so.

Let me think for a while.

Shin

• ###### 5. Re: Subtotal for Top N within a category

In TOP N's case, instead of sum, you need to use running_sum.

<<Basic Concept.>>

<<to Only show last row of each category>>

[Sub Total Display]

if [Calculation1]=[Top X]

then (

if ([Calculation1])<=([Top X])

then RUNNING_sum(sum([Sales])) END

) end

Thanks,

Shin

9.1 attached

1 of 1 people found this helpful
• ###### 6. Re: Subtotal for Top N within a category

Thank you Shin, that helped.

• ###### 7. Re: Subtotal for Top N within a category

Hi Shin,

any idea how to let users export the underlying data for the top X list only ??

Is there a way to create a button to do the export ??