+ Reply to Thread
Results 1 to 6 of 6

Pivot Table average in stead of Grand Total

  1. #1
    Registered User
    Join Date
    03-15-2019
    Location
    Pretoria, South Africa
    MS-Off Ver
    MS Office 2016
    Posts
    6

    Pivot Table average in stead of Grand Total

    Hi,

    I am trying to get my pivot table to average the last 5 weeks average to compare it to the current weeks sales.
    Could this be done? The attachment is how the pivot table should be.


    Regards,
    Attached Files Attached Files

  2. #2
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Pivot Table average in stead of Grand Total

    If you add a "Category" helper-column to your data, you can get the results you want.

    Based on the date, the Category column formula would return:
    Curr
    AvgRng
    Other

    Then your pivot table can use that Category in the column section and average the values.
    Since the Curr category would only have one date, the average would be the total.
    The AvgRng would have 5 dates and calculate their average.
    Hide the columns you don't need.

    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Registered User
    Join Date
    03-15-2019
    Location
    Pretoria, South Africa
    MS-Off Ver
    MS Office 2016
    Posts
    6

    Re: Pivot Table average in stead of Grand Total

    Hi Ron,

    Thank you for your feedback, but the truth of the matter is that I have report on thousand of stores and products. My true average range will consist of a 25 period average. So to hide all the columns will be a time consuming exercise.
    My Pivot Table is based on data in a Ms Access Table. I thought that it will be better to group everything in access with queries and just pull that information to excel for charting. But I am pinned there as well, as the sql query doesn't allow me to run aggregated data. Could be the users fault as my skill in Access is a little bit rusty.

    Regards,

    Willem

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,699

    Re: Pivot Table average in stead of Grand Total

    Hello Wilem87 and Welcome to Excel Forum.
    There may be more to this than I am considering, however I have attempted to construct the a table raw data that might feed a pivot table such as the sample shown in the file. Note that the table was produced using Power Query which is a standard feature in the 2016 version of Excel. The dates in the resulting Pivot Table can be changed using Date filters (cell F2) > Between. I you choose the dates 2/10/2019 and 3/17/2019 you'll see that average of the previous five weeks updates. The formula used for previous five weeks is the same as in the sample.
    Perhaps if you could upload a desensitized sample of the raw data or Access table we could be of more help.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    03-15-2019
    Location
    Pretoria, South Africa
    MS-Off Ver
    MS Office 2016
    Posts
    6

    Re: Pivot Table average in stead of Grand Total

    Hi JeteMc,

    Thank you for your feedback and solution.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,699

    Re: Pivot Table average in stead of Grand Total

    You're Welcome. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

+ 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 Grand Total Average
    By temexcel in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-16-2014, 02:32 PM
  2. Pivot Table, Need Grand Total as an average
    By children in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 02-13-2014, 01:37 PM
  3. [SOLVED] Is it possible to have both Grand Total and Grand Average in a Pivot Table?
    By ANS in forum Excel Charting & Pivots
    Replies: 15
    Last Post: 10-16-2012, 05:15 AM
  4. Pivot Table Grand Total on Average
    By kmlloyd in forum Excel General
    Replies: 0
    Last Post: 03-16-2011, 12:21 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