+ Reply to Thread
Results 1 to 4 of 4

Cannot drill into pivot table grand totals

  1. #1
    Forum Contributor
    Join Date
    04-13-2010
    Location
    England
    MS-Off Ver
    Office 2016 365
    Posts
    211

    Question Cannot drill into pivot table grand totals

    It's been a long time but i am back using Excel so i find myself back here! I have created a report workbook for users that gives them a control page with several slicers. The slicers update pivot reports on each sheet. The pivot tables have been added to the data model so that a single slicer can be linked to more than one pivot.

    What my users wants to do is amend the slicers to a certain selection and then double click the grand total on the pivot table to see the data rows that make up the grand total number.


    Unfortunately, Excel presents the message "You cannot use Show Details on a Grand Total value when a filter is applied to the row or column fields. To use this command, remove the filter"

    But the whole idea of the workbook is so that they can segment the data they want and then interrogate the detail. Removing the filters would defeat the point.

    Any ideas on a solution? Example attached
    Attached Files Attached Files

  2. #2
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Cannot drill into pivot table grand totals

    Bit of a pain, but you could duplicate the fields you want to filter on, and then attach the slicers to the duplicate, not to the field that is displayed in the pivot(s). It does mean you end up with duplicated columns in the drilldown though.
    Rory

  3. #3
    Forum Contributor
    Join Date
    04-13-2010
    Location
    England
    MS-Off Ver
    Office 2016 365
    Posts
    211
    Quote Originally Posted by rorya View Post
    Bit of a pain, but you could duplicate the fields you want to filter on, and then attach the slicers to the duplicate, not to the field that is displayed in the pivot(s). It does mean you end up with duplicated columns in the drilldown though.
    Mmm... It's a bit messy and suboptimal but could give a solution. I'll try it when I'm back at work (I'm UK)....

  4. #4
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Nowy Tomysl, Poland
    MS-Off Ver
    2019, O365
    Posts
    398

    Re: Cannot drill into pivot table grand totals

    My suggestion - create a pivot table without loading data into the data model. Then you will achieve your goal for the filtered pivot table.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Pivot Table Grand total issue need two grand totals
    By alesha711 in forum Excel Charting & Pivots
    Replies: 18
    Last Post: 02-13-2019, 03:02 PM
  2. [SOLVED] pivot table grand total drill down
    By hftechno in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-30-2018, 11:24 PM
  3. Pivot Table Percentage of grand totals listed under the grant totals
    By Biker102 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 05-31-2017, 01:31 PM
  4. Need help with grand totals in pivot table
    By Missygal in forum Excel General
    Replies: 1
    Last Post: 05-16-2006, 02:10 PM
  5. Pivot Table Grand Totals??
    By Adam in forum Excel General
    Replies: 0
    Last Post: 03-25-2005, 01:06 PM
  6. Pivot Table Grand Totals
    By Adam in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-25-2005, 01:06 PM
  7. Pivot Table Grand Totals
    By gboulton in forum Excel General
    Replies: 1
    Last Post: 03-16-2005, 08:34 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1