# Sum Specified Value in Filter

• ### Question

• Hi,

I am doing some work in a Linked Table pulled out of a Data Model and would like to add some subtotal rows underneath the table. What I would like one of the rows to have is a sum of the top 30 values in column "Forecast Gross Sales", adapting to whatever filters I select. I'm entering the formula below, but keep encountering an error:

=SUBTOTAL(9,LARGE(Table1_13[Forecast Gross Sales],{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30}))

The next subtotal row I'm attempting to add is the subtotal of the Forecast Gross Sales column * 20%. I figure this would be simple to do once I understand the root to the issue above but any additional assistance is greatly appreciated.

Thank you,

Marlon

Monday, March 31, 2014 4:35 PM

• Hey Marlon,

I was just thinking about another approach which led me to this post:

http://tinyurl.com/prashmr

It somehow goes the same direction.

All in all I would build a measure using: RANKX, SUM or SUMX and eventually ALLSELECTED.

Do you have a working copy of your power pivot model you could share so we could help even more ;-)

I hope I was of some help!

___________________________________

• Proposed as answer by Monday, April 7, 2014 11:13 AM
• Marked as answer by Wednesday, April 9, 2014 6:42 AM
Tuesday, April 1, 2014 12:02 PM

### All replies

• Hey Marlon,

you didn't state which reporting-tool you're using, thus I assume you're talking about Excel's pivot-functionality.

A super easy-way to achieve what you want would be the Top-N functioanlity in Excel Pivot. See here: http://tinyurl.com/7yxtwlx

It would automatically adapt to whatever filter you make.

This approach will not solve your second wish. Moreover I doubt that you can accomplish this in Excel in general. I think you will have to create a new measure which will then be another column in your pivot-table.

I hope I was of some help!

___________________________________

Tuesday, April 1, 2014 11:25 AM
• Hey Marlon,

I was just thinking about another approach which led me to this post:

http://tinyurl.com/prashmr

It somehow goes the same direction.

All in all I would build a measure using: RANKX, SUM or SUMX and eventually ALLSELECTED.

Do you have a working copy of your power pivot model you could share so we could help even more ;-)

I hope I was of some help!

___________________________________