+ Reply to Thread
Results 1 to 4 of 4

Excel Pivot add yearly total column to monthly pivot

  1. #1
    Registered User
    Join Date
    02-17-2014
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    15

    Excel Pivot add yearly total column to monthly pivot

    Hello,

    I have a Power Pivot which displays monthly budget per project.
    I am using months & years slicers to filter the data.
    The Budget column displays total Budget for selected months & years per project.
    I need to add total yearly budget column which will display in each row the total budget for selected years (will not refer to months filters).

    How can I do it?

    I thought to add calculated field (something like sumifs) that will calculated the yearly data based on budget sheet/table.
    But I do know how to define such calculated field.

    I added a sample workbook - I need to add the yellow column to the pivot using calculated fields or other way. How can I do it ?

    Thank you for your help
    Attached Files Attached Files
    Last edited by avism; 05-08-2020 at 04:54 AM.

  2. #2
    Registered User
    Join Date
    02-17-2014
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Excel Pivot add yearly total column to monthly pivot

    I appreciate your help. I added a sample workbook.

    Thank you

  3. #3
    Registered User
    Join Date
    01-24-2020
    Location
    Belgium
    MS-Off Ver
    2013
    Posts
    95

    Re: Excel Pivot add yearly total column to monthly pivot

    Hi, there

    As far as I know, Power Pivot table comes into play.

    Compile these 2 measures after importing the dataset into Power Pivot data model.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    The required results are achieved and shown in the power pivot table then.

    Pls refer to the attached file for detail.
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    11-11-2012
    Location
    Muscat, Oman
    MS-Off Ver
    Office 365
    Posts
    521

    Re: Excel Pivot add yearly total column to monthly pivot

    Hi,
    1. Select any cell in the year column. Right Click and select sub total year
    2. Drag down budget once again in values area (New column will read sum of budget 2). Right Click
    and select show value as % of parent total and select base field as year.

    Hope this helps!
    Best Regards/VKS

+ 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. Macro updating of invoice sheet monthly yearly and total
    By omarsam in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-14-2016, 04:02 AM
  2. Pivot chart from monthly to yearly
    By gengiskhan in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 10-22-2014, 05:54 PM
  3. Replies: 1
    Last Post: 03-08-2013, 04:18 PM
  4. Replies: 5
    Last Post: 03-07-2013, 11:38 AM
  5. Replies: 3
    Last Post: 02-18-2013, 09:12 PM
  6. Pivot Table - Monthly Running Total on a percentage
    By Wayne Dunlop in forum Excel General
    Replies: 3
    Last Post: 09-14-2012, 06:19 AM
  7. Daily, Monthly, Yearly Pivot Tables
    By danifani in forum Excel General
    Replies: 8
    Last Post: 09-23-2011, 06:06 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