+ Reply to Thread
Results 1 to 8 of 8

Percent difference in a Pivot Table

  1. #1
    Forum Contributor
    Join Date
    09-15-2005
    Location
    Florida
    MS-Off Ver
    Excel Professional Plus 2016
    Posts
    142

    Percent difference in a Pivot Table

    With all the hours of video watching on YouTube, I have not been able to extrapolate a solution to my created Pivot table. I did learn a lot on how to do it using there examples lol. Having said the above, I need some sleep and if anyone can rescue me I would be truly appreciative.

    I have a Data table example as follow with Columns as an example: ( have uploaded the spreadsheet with the real info)

    Analyst County Compendium WK 06 WK 07 WK 08 WK 09 WK 10
    Fred Dade Labs 4,000 7,000 3,000 2,000 5,000

    After creating a pivot table, I have it setup as follows:

    Analyst and Compendium in the: Row section
    Week 7 and Week 6 in the: Value section

    Trying to put a comparison with maybe conditional icons show increase , decrease that has the percent difference. AS a bonus, a dynamic one would be awesome but beggars can't be choosers.

    Not my job but helping impact patient care if this makes a difference weather you help me or not.

    Cordially,

    Robert
    Attached Files Attached Files
    Last edited by rogrand; 02-16-2019 at 03:50 PM.

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

    Re: Percent difference in a Pivot Table

    You need to get your data into normalized format. If you have Excel 2016 you can use PowerPivot. Otherwise use the application here: https://www.excelforum.com/tips-and-...zing-data.html.

    Once in this format, you can use Pivot tables more efficiently .
    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 Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Percent difference in a Pivot Table

    I think this might be what you want. If it is and you need an explanation, let me know.
    Attached Files Attached Files
    Last edited by dflak; 02-16-2019 at 09:55 PM.

  4. #4
    Forum Contributor
    Join Date
    09-15-2005
    Location
    Florida
    MS-Off Ver
    Excel Professional Plus 2016
    Posts
    142

    Re: Percent difference in a Pivot Table

    Wow. Powerpivot is a whole other different animal but love it!!!!! . I can use this but I was not able to duplicate the direction of the arrows(not same as you) but got close. the yellow horizontal will not come up for me. Sheet 3, I was not even close to it. Can't figure out ow you used powerpivot to get the weeks in the value fields as a table

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

    Re: Percent difference in a Pivot Table

    I'll get back to you on Monday. I'm just figuring out PowerPivot myself. Few of my "customers" have 2016.

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

    Re: Percent difference in a Pivot Table

    If you just want to compare week 7 to week 6, you can do that even with the layout you have by using a calculated field. See attached.
    Attached Files Attached Files
    Rory

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

    Re: Percent difference in a Pivot Table

    Here is what I did

    Clicked on Data -> From Table Range

    Power Pivot launched

    Deleted all columns without data (From F on)

    Click Transform Tab

    Selected Columns WK 06 and WK 07 and then clicked on Unpivot Columns

    Selected Close and Load and results came out on Sheet1 with the name Table_2.

    I used this table as my pivot source

    Analyst, County and Compendium as row headers

    Attribute (week number) as a column header

    And the value twice as values

    For the second value I selected Difference from previous Attibute

    Highlighted the column and selected Conditional Formatting

    I selected Icon sets with the arrows

    I Edited the set to number > 0 for up arrow and >= 0 for second number.

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

    Re: Percent difference in a Pivot Table

    Instead of Difference from previous attribute you can show values as percent difference from previous attribute. You will get some #DIV0! errors but you can "blank" these out or make them zero with Pivot Table Options

+ 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. Add simple a1/b1=c1 as percent in pivot table
    By cvbs in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 06-10-2018, 03:27 PM
  2. Percent of 2 columns in pivot table..Need Urgent Help
    By saikatray in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 08-31-2017, 08:25 AM
  3. Percent Running Total in Pivot Table
    By ChrisJones123 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 08-27-2015, 03:43 PM
  4. Pivot Table Calc showing Percent
    By Ironman in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-27-2012, 11:30 AM
  5. Pivot Table Percent Of
    By yawnzzzz in forum Excel General
    Replies: 3
    Last Post: 02-17-2011, 11:07 AM
  6. Pivot Table - Percent
    By yawnzzzz in forum Excel General
    Replies: 2
    Last Post: 06-30-2010, 01:10 PM
  7. Replies: 1
    Last Post: 11-23-2007, 03:11 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