+ Reply to Thread
Results 1 to 7 of 7

Pivot Table with % difference from

  1. #1
    Registered User
    Join Date
    11-15-2012
    Location
    Middle East
    MS-Off Ver
    Excel 2010
    Posts
    60

    Pivot Table with % difference from

    Hi guys,

    I'd like to have a pivot table which its values are based on the %difference from the column total.
    In the attached file for example, I manually created table 2 based on table 1 base, but I'd like to create table 2 using pivot. you can see for example that cell C21 (colored in yellow) is calculated by C5/C12.
    I tried using for this one in pivot option "show values as ->%difference from", but it didn't work for me.

    Can anyone please assist?


    Thank you very much,

    Moty
    Attached Files Attached Files

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

    Re: Pivot Table with % difference from

    I think this might be what you want. It's essentially the same spreadsheet except the items are expressed as percent of columns total.
    Attached Images Attached Images
    Attached Files Attached Files
    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
    Registered User
    Join Date
    11-15-2012
    Location
    Middle East
    MS-Off Ver
    Excel 2010
    Posts
    60

    Re: Pivot Table with % difference from

    Thanks dflak, but I'm actually looking for something else.

    I'm looking for the difference in % between the value which appears in the table's column to the column's total.
    So I'm looking to actually create table 2 by using pivot table options.

    I'd appreciate any assistance. Thanks!

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,423

    Re: Pivot Table with % difference from

    OK, so instead of percent buy row total, we do percent by column total.
    Attached Files Attached Files

  5. #5
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Pivot Table with % difference from

    Hi,

    I do not believe that will be possible in one pivot table using regular pivot tables. I think it would require Power Pivot.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  6. #6
    Registered User
    Join Date
    11-15-2012
    Location
    Middle East
    MS-Off Ver
    Excel 2010
    Posts
    60

    Re: Pivot Table with % difference from

    Thanks xlnitwit.
    Can you/someone else elaborate on how can it be done using Power Pivot?

  7. #7
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Pivot Table with % difference from

    Here is your workbook showing the measures required in Power Pivot for 2010. Not all the measures are strictly necessary but I find it easier to use more, smaller steps.

+ 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. PIVOT TABLE Caculating Difference
    By omeshanker in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 09-16-2013, 12:53 PM
  2. Pivot Table to Show difference between Min and Max Value
    By bigroo1958 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 02-20-2013, 09:06 PM
  3. Replies: 2
    Last Post: 02-21-2012, 12:49 PM
  4. Var difference on Pivot table
    By gico1972 in forum Excel General
    Replies: 5
    Last Post: 12-21-2010, 01:10 PM
  5. Daily Difference in Pivot Table
    By agentred in forum Excel General
    Replies: 2
    Last Post: 01-06-2010, 03:24 PM
  6. Pivot Table - Difference Between Totals
    By NickHubble in forum Excel General
    Replies: 4
    Last Post: 07-21-2009, 05:41 AM
  7. [SOLVED] Pivot Table Subtotals - Difference
    By Marcotte A in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-23-2005, 01:35 PM
  8. Difference between Averages in a Pivot Table
    By jbesr1230@hotmail.com in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-04-2005, 12:06 AM

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