+ Reply to Thread
Results 1 to 8 of 8

Help with calculating the increase or decrease in pivot table

  1. #1
    Forum Contributor
    Join Date
    06-17-2014
    Location
    london
    MS-Off Ver
    365
    Posts
    398

    Help with calculating the increase or decrease in pivot table

    Hi All

    I need help with the following problem and any advise is appreciate it:

    I have a large power Pivot report whci shows the Department Numbers and number of stock (some can be negetive).
    I have designed a power Pivot report which has week NO as Column Labels and shows the stocks for each dept No for each week.

    What I need is a comparison to see whether the stock has gone up or down compare to the previous week.

    I have included a very simple example with normal pivot to see whether I can do this. In this example(in sheet2) I need to subtract the figure for week 25 from Week 24 and show it in a new column (we can name it as progress or anyhting else). and then for week 26 I need to compare w26 (when I get the w26 results) results with w25. My actual filile is power pivot with lots of data. I just want to see how to do this so I can imply it to the main file.

    One way which will be acceptable fo my manager is to show the increase or decrease as conditional formatting so that show the increase or decraese with a flag but it should always compare the current week against previous weeks numbers. I hpe this makes sense.

    Any help is greatfully apprecaite it.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    07-21-2015
    Location
    London, Canada
    MS-Off Ver
    2013
    Posts
    4

    Re: Help with calculating the increase or decrease in pivot table

    Hi rv02:

    You probably already thought of this but I think this could be done using a calculated item.

    The downside is that you would have to change the calculated item when the new week's data becomes available.

    bdika

  3. #3
    Forum Contributor
    Join Date
    06-17-2014
    Location
    london
    MS-Off Ver
    365
    Posts
    398

    Re: Help with calculating the increase or decrease in pivot table

    Hi bdika
    Would it be possible to give me an example of this in the spreadsheet provided. Thanks in advance

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Help with calculating the increase or decrease in pivot table

    Hi,

    See if this is what you want. I did it outside the pivot table just to see if this is the answer you seek.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  5. #5
    Forum Contributor
    Join Date
    06-17-2014
    Location
    london
    MS-Off Ver
    365
    Posts
    398

    Re: Help with calculating the increase or decrease in pivot table

    This is basically what I need but I need to know how to include this within the pivot as a calculated field or measure , I don't mind updating the data formula every week

  6. #6
    Forum Contributor
    Join Date
    06-17-2014
    Location
    london
    MS-Off Ver
    365
    Posts
    398

    Re: Help with calculating the increase or decrease in pivot table

    Just to be clear I need , if poible, to create a calculated field or column (whichever is possible) to calculate the difference between stocks for each week. i.e. subtract the value in week 25 from value in week 24 for each department. Any help is appreciate it, I do not mind updating the formula each week.

  7. #7
    Registered User
    Join Date
    07-21-2015
    Location
    London, Canada
    MS-Off Ver
    2013
    Posts
    4

    Re: Help with calculating the increase or decrease in pivot table

    Hi rv02:

    Attached is your sample sheet showing a calculated item in the pivot table.

    bdika
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    06-17-2014
    Location
    london
    MS-Off Ver
    365
    Posts
    398

    Re: Help with calculating the increase or decrease in pivot table

    Thank you very much for your help

+ 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. Replies: 1
    Last Post: 07-20-2015, 01:53 PM
  2. Replies: 3
    Last Post: 09-18-2012, 01:01 AM
  3. Calculating increase/decrease in monthly sales
    By Climaxgp in forum Excel General
    Replies: 1
    Last Post: 05-10-2012, 05:21 AM
  4. Replies: 2
    Last Post: 07-21-2010, 12:54 PM
  5. % Increase and decrease.
    By DODGE in forum Excel General
    Replies: 5
    Last Post: 03-02-2010, 05:01 PM
  6. Calculating Percentage increase or decrease
    By RTPCT in forum Excel General
    Replies: 1
    Last Post: 07-13-2007, 09:27 AM
  7. % of increase or decrease
    By Neil R in forum Excel General
    Replies: 9
    Last Post: 11-26-2005, 11:15 PM

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