+ Reply to Thread
Results 1 to 5 of 5

PivotTable - Using Grand Total & Formatting Columns Outside Pivot

  1. #1
    Registered User
    Join Date
    09-21-2015
    Location
    Always, Learning
    MS-Off Ver
    2016
    Posts
    17

    Cool PivotTable - Using Grand Total & Formatting Columns Outside Pivot

    Hello,

    This is an example of what my data looks like:

    Name|Counts|Amount
    John|1|100
    Fred|2|200
    Bob|3|400
    John|4|500

    1. I need the Average Amount. How do I take the Amount and Divide by the Grand Total of Counts? Because there's filters on the data, when I 'Summarize Values by AVG' it calcualtes the Average for every John, Fred, Bob in the Pivot.

    2. I created another column outside the PivotTable to manually calculate things. How do I ensure the PivotTable format aligns with this column? When I apply a new filter, the manual column will change and doesn't align with the Pivot Table Subtotal formatting etc.

    Thanks,
    Sharp.

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

    Re: PivotTable - Using Grand Total & Formatting Columns Outside Pivot

    Hi Sharp,

    See the attached. Is this what you need? You simply right click on a value and then Show as or ...

    Pivot Table Examples.xlsx
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

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

    Re: PivotTable - Using Grand Total & Formatting Columns Outside Pivot

    I used Marvin's file
    Is that what you want?
    (manually, with PowerPivot or from source table)
    Attached Files Attached Files
    Last edited by sandy666; 09-06-2017 at 03:55 AM.

  4. #4
    Registered User
    Join Date
    09-21-2015
    Location
    Always, Learning
    MS-Off Ver
    2016
    Posts
    17

    Re: PivotTable - Using Grand Total & Formatting Columns Outside Pivot

    Quote Originally Posted by sandy666 View Post
    I used Marvin's file
    Is that what you want?
    (manually, with PowerPivot or from source table)
    Hi Sandy,
    The PowerPivot Second Example is exactly what I need. How do I go about creating something like this?

    Regards,
    Omar

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

    Re: PivotTable - Using Grand Total & Formatting Columns Outside Pivot

    Add source table to Data Model
    click on the right side in new column (it exist) then in formula bar type: =[Amount]/SUMX(table2,[counts])
    enter
    then create PivotTable (Home - Pivot Table)
    all from PowerPivot

    Your table can be with different name, eg. Table3 or something (you can see name at the bottom tab)

    ---
    If that takes care of your original question, & to say Thanks, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
    then select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.
    If you did it - ignore it.
    Thank you.
    Last edited by sandy666; 09-06-2017 at 10:56 AM. Reason: typos

+ 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. PivotTable sub-total/grand total percentage issue
    By Jess0121 in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 04-19-2016, 05:59 PM
  2. Replies: 9
    Last Post: 03-18-2015, 07:16 AM
  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. Replies: 0
    Last Post: 10-12-2012, 07:24 PM
  5. How to get rid of N/A in Grand Total line of Pivottable
    By JasonBarnes in forum Excel General
    Replies: 1
    Last Post: 01-06-2011, 01:08 PM
  6. [SOLVED] Remove PivotTable Grand Total
    By Desmond in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-01-2006, 01:40 PM
  7. [SOLVED] Pivot Grand total on Selected columns only
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-30-2005, 02:40 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