+ Reply to Thread
Results 1 to 4 of 4

Creating Groups in Pivot

  1. #1
    Registered User
    Join Date
    11-23-2017
    Location
    Germany
    MS-Off Ver
    2019
    Posts
    51

    Creating Groups in Pivot

    Hi all,

    I have a data source, with various projects (rows) and sub-projects (new column, various rows) and various budget columns and a cost column per year for various years (please see simplified version attached).

    I have created a calculated field per year, but is there a way for me to group the years to show only the result unless I expand it? I have too many years a different columns per year so when it shows all its very confusing. I was trying to see if I can do this with a Power Pivot, but I am not an expert in it. Any ideas?

    Thanks a lot.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Creating Groups in Pivot

    Using Power Query (a free add-in for the 2013 version of Excel From Microsoft) you could produce a row over row table of data using the following steps:
    1. Convert A1:J11 to a table (Ctrl + t)
    2. From the Data tab choose From Table/Range
    3. Paste the following into the Power Query advanced editor:
    Please Login or Register  to view this content.
    4. Close and Load to a new worksheet (as modeled in columns A:E on Sheet2
    5. Produce a pivot table from the new table (also modeled on Sheet2)
    Note that the new column, Year, is used for the filter.
    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.

  3. #3
    Registered User
    Join Date
    11-23-2017
    Location
    Germany
    MS-Off Ver
    2019
    Posts
    51

    Re: Creating Groups in Pivot

    Quote Originally Posted by JeteMc View Post
    Using Power Query (a free add-in for the 2013 version of Excel From Microsoft) you could produce a row over row table of data using the following steps:
    1. Convert A1:J11 to a table (Ctrl + t)
    2. From the Data tab choose From Table/Range
    3. Paste the following into the Power Query advanced editor:
    Please Login or Register  to view this content.
    4. Close and Load to a new worksheet (as modeled in columns A:E on Sheet2
    5. Produce a pivot table from the new table (also modeled on Sheet2)
    Note that the new column, Year, is used for the filter.
    Let us know if you have any questions.
    Sorry for the late reponse. Thanks a lot!

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Creating Groups in Pivot

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. 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 with Sub-Groups repeating for all Major Groups error
    By Carla77 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 05-24-2020, 01:38 PM
  2. [SOLVED] Creating a dynamic calculation within groups
    By Isara-NJ in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 04-13-2017, 01:37 PM
  3. Replies: 1
    Last Post: 07-22-2015, 07:16 AM
  4. Replies: 0
    Last Post: 09-09-2014, 07:38 AM
  5. Creating Number Sequence in Groups of 10
    By DBWalsh12 in forum Excel General
    Replies: 8
    Last Post: 02-21-2013, 05:55 PM
  6. Replies: 0
    Last Post: 01-20-2012, 07:44 AM
  7. Creating Groups
    By KINKASI in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-13-2009, 06: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