locked
Sum Specified Value in Filter RRS feed

  • 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

Answers

  • 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!

    ___________________________________

    Kind regards, please mark helpful posts! :)

    • Proposed as answer by Elvis Long Monday, April 7, 2014 11:13 AM
    • Marked as answer by Elvis Long 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!

    ___________________________________

    Kind regards, please mark helpful posts! :)

    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!

    ___________________________________

    Kind regards, please mark helpful posts! :)

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