+ Reply to Thread
Results 1 to 10 of 10

Pivot Table AVG based on Subtotals

  1. #1
    Registered User
    Join Date
    01-02-2013
    Location
    NY
    MS-Off Ver
    Excel Office 365
    Posts
    49

    Pivot Table AVG based on Subtotals

    I am sure this has been brought up before. However, I have looked for hours on the internet and here:

    Attached is data and pivot to explain my issue. I have also added a column 'SHOULD BE' and placed the values that I am expecting to get.

    I am trying to get the pivot table to subtotal and show the avg of the items by day. However, the pivot calculates the subtotal average based on the raw data, not on the pivot table data. For example:

    DAY ITEM AVG VALUE SHOULD BE
    Monday Widget 1 1.33
    Monday Widget 2 1.00
    Monday Widget 3 4.00
    Monday Total 1.80 2.11

    The pivot averages as 1.80 when I would like it to average these 3 values to then equal 2.11.



    Any help would be appreciated. Thank you.
    Attached Files Attached Files

  2. #2
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Pivot Table AVG based on Subtotals

    is that what you want?
    if yes use PowerQuery (Get&Transform)
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    01-02-2013
    Location
    NY
    MS-Off Ver
    Excel Office 365
    Posts
    49

    Re: Pivot Table AVG based on Subtotals

    This is exactly what I wanted. However, could you briefly explain how you did the PowerQuery Get/Transform to get to this point?

    Thank you!

  4. #4
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Pivot Table AVG based on Subtotals

    briefly? sure
    go to ribbon Data tab
    show queries (you will see workbook queries)
    dbl click on table there
    PQ editor will open and again on the right side you will see steps
    group by Day and Item and average value (see picture)

    group.jpg

    then insert PivotTable and...

    insertpt.jpg

    the rest you know

    PS. Hope you know how to load source to PowerQuery

  5. #5
    Registered User
    Join Date
    01-02-2013
    Location
    NY
    MS-Off Ver
    Excel Office 365
    Posts
    49

    Re: Pivot Table AVG based on Subtotals

    Bravo. Thank you.

  6. #6
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Pivot Table AVG based on Subtotals

    you are welcome

    If that takes care of your original question, & to say Thanks, please
    1. click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
    If you did it already - ignore it.
    Thank you.

  7. #7
    Registered User
    Join Date
    01-02-2013
    Location
    NY
    MS-Off Ver
    Excel Office 365
    Posts
    49

    Re: Pivot Table AVG based on Subtotals

    Done. Thanks again. Appreciate it.

  8. #8
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Pivot Table AVG based on Subtotals

    Thanks

    Have a nice day

  9. #9
    Forum Contributor
    Join Date
    09-15-2011
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    135

    Re: Pivot Table AVG based on Subtotals

    Hi Sandy,

    Good day,

    Please help me to where can i get the below said information, Myslef unable to find the info.

    show queries (you will see workbook queries)
    dbl click on table there
    PQ editor will open and again on the right side you will see steps

    Thanks
    Raju

  10. #10
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Pivot Table AVG based on Subtotals

    @rajuganapathy

    Power Query for
    your profile says: 2003

+ 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 subtotals
    By laliparker in forum Excel Charting & Pivots
    Replies: 10
    Last Post: 11-28-2013, 10:39 AM
  2. Subtotals in a pivot table
    By pa56 in forum Excel General
    Replies: 1
    Last Post: 11-05-2009, 12:43 PM
  3. Pivot Table subtotals
    By tigertim71 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 06-27-2008, 03:40 PM
  4. Pivot table subtotals
    By RhysPieces in forum Excel General
    Replies: 2
    Last Post: 05-04-2006, 12:00 PM
  5. [SOLVED] Pivot Table Subtotals
    By vcurban in forum Excel General
    Replies: 4
    Last Post: 03-01-2006, 09:10 PM
  6. [SOLVED] Subtotals in a Pivot Table
    By MarkM in forum Excel General
    Replies: 0
    Last Post: 12-20-2005, 12:10 PM
  7. pivot table subtotals
    By cokerrm in forum Excel General
    Replies: 0
    Last Post: 03-15-2005, 12:41 AM
  8. [SOLVED] Pivot Table subtotals
    By Linda in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-08-2005, 09:06 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