+ Reply to Thread
Results 1 to 3 of 3

Show Pivot values as % of actual total when filtered for TOP-N items

  1. #1
    Forum Contributor
    Join Date
    06-01-2014
    Location
    Riyadh, Saudi Arabia
    MS-Off Ver
    MS Office 2010
    Posts
    201

    Question Show Pivot values as % of actual total when filtered for TOP-N items

    I have a Pivot table of products in rows and Sales, COGS & Margin in columns.

    I have filtered the Pivot Table to show only To 10 Items based on highest sales in Descending Order.

    Now I want to show for each product what is the percentage of its sales compared to Sales of all Products.

    When I select show value as % Grand Total or Column Total it will not reflect the proper number as its basing on the totals of Filtered Top items only.

    How do I change it to show the % based in the sales of all Products ?

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,124

    Re: Show Pivot values as % of actual total when filtered for TOP-N items

    Without resorting to some trickery with the source data, you can't.

    One workaround is to sort the data by whatever metric is used to determine the top 10 and hide the other rows.

    If you are trying to plot the top 10 on a chart, then you can overlay the results of the pivot table with named dynamic ranges.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Forum Contributor
    Join Date
    06-01-2014
    Location
    Riyadh, Saudi Arabia
    MS-Off Ver
    MS Office 2010
    Posts
    201

    Re: Show Pivot values as % of actual total when filtered for TOP-N items

    Thanks for the update.

+ 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. How can I show the total sum of items in a filtered column
    By Roger Govier in forum Excel Formulas & Functions
    Replies: 24
    Last Post: 09-06-2005, 03:05 PM
  2. [SOLVED] How can I show the total sum of items in a filtered column
    By huckie in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 PM
  3. [SOLVED] How can I show the total sum of items in a filtered column
    By Roger Govier in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 09-06-2005, 07:05 AM
  4. [SOLVED] How can I show the total sum of items in a filtered column
    By Roger Govier in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-06-2005, 06:05 AM
  5. How can I show the total sum of items in a filtered column
    By Roger Govier in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 09-06-2005, 04:05 AM
  6. [SOLVED] How can I show the total sum of items in a filtered column
    By huckie in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  7. How can I show the total sum of items in a filtered column
    By huckie in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM
  8. How can I show the total sum of items in a filtered column
    By huckie in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM

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