+ Reply to Thread
Results 1 to 15 of 15

Help with Pivot table - possibly calculated item or field?

  1. 03-06-2024, 12:51 AM #1
    MaheshK5277
    MaheshK5277 is offline
    Forum Contributor
    Join Date
    12-18-2015
    Location
    Mumbai, India
    MS-Off Ver
    MS Office 365
    Posts
    136

    Help with Pivot table - possibly calculated item or field?

    Hi Experts,

    Below is the snapshot of a pivot table (the source data is extracted from a system application). I need to pull every month report for the 'PTP's ( which are basically projects with unique IDs). The report needs to be pulled month-wise. For e.g., as of now, i need a monthly report for Feb-24 & the pivot table should only display the data for all PTP's which are finished in Feb-24 (anywhere between 1-Feb-24 to 29-Feb-24). Further, i need to get subtotal for each of the statuses like 'Complete', 'Live', 'On hold', 'cancelled' only for the month of Feb-24. Likewise, i will need to pull report for every month & hence looking for a formula to achieve this.

    Can someone help please, a little urgent.

    NOTE: I am unable to upload the actual file as the data is client confidential. But i have attached a snapshot (the data sit in columns from A to E) as shown below.


    Thanks,
    Mahesh
    Attached Images Attached Images
    Reply With QuoteRegister To Reply
  2. 03-06-2024, 04:09 AM #2
    MaheshK5277
    MaheshK5277 is offline
    Forum Contributor
    Join Date
    12-18-2015
    Location
    Mumbai, India
    MS-Off Ver
    MS Office 365
    Posts
    136

    Re: Help with Pivot table - possibly calculated itemor field?

    Hi Experts,

    Can someone please guide me on this? I am stuck. I tried with label filters on the pivot table, which works...but am looking for a more optimum way (may be through a calculated item?) not sure

    Thanks,
    Mahesh
    Reply With QuoteRegister To Reply
  3. 03-06-2024, 04:33 AM #3
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,850

    Re: Help with Pivot table - possibly calculated itemor field?

    Administrative Note:

    It has been only about three since you last posted. Please do not bump threads until 24 hours have passed, and then only once each day. By bumping the post, you have removed it from the Unanswered Threads filter that many forum members use to find posts that need attention. We are all volunteers here who have lives beyond this forum. While you may be awake and working, this is a world wide forum and others may be playing, sleeping or working and not have the time to look into your issues this minute. Patience is important here. If you have an urgent issue, you may wish to look at hiring a professional consultant in your area. Again, please do not bump threads more often than once in every 24 hours: if you get the help you need today, then great, but you really should not count on it. Thanks for your understanding and patience.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  4. 03-06-2024, 10:47 AM #4
    MaheshK5277
    MaheshK5277 is offline
    Forum Contributor
    Join Date
    12-18-2015
    Location
    Mumbai, India
    MS-Off Ver
    MS Office 365
    Posts
    136

    Re: Help with Pivot table - possibly calculated itemor field?

    Hi Aligw,

    Ok...sorry for the inconvenience caused. Will follow the note as instructed now onwards.

    Thanks,
    Mahesh
    Reply With QuoteRegister To Reply
  5. 03-08-2024, 05:41 AM #5
    rorya
    rorya is offline
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent Monthly Channel / Insiders Beta
    Posts
    8,913

    Re: Help with Pivot table - possibly calculated itemor field?

    You could just move the status field to the columns area, and then filter the Finish field for 'This month'
    Rory
    Reply With QuoteRegister To Reply
  6. 03-08-2024, 09:13 AM #6
    MaheshK5277
    MaheshK5277 is offline
    Forum Contributor
    Join Date
    12-18-2015
    Location
    Mumbai, India
    MS-Off Ver
    MS Office 365
    Posts
    136

    Re: Help with Pivot table - possibly calculated itemor field?

    Hi Rorya,

    OK..i moved the status to column. The question now is - can we apply a filter on the status column (something like -'as of today') which will get me combined results for all statuses ('complete', 'Live', 'On-hold') at once? I have attached a snapshot for better understanding.

    Thanks,
    Mahesh
    Attached Images Attached Images
    Reply With QuoteRegister To Reply
  7. 03-08-2024, 09:59 AM #7
    rorya
    rorya is offline
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent Monthly Channel / Insiders Beta
    Posts
    8,913

    Re: Help with Pivot table - possibly calculated itemor field?

    You need to filter the Finish column for the date you want.
    Reply With QuoteRegister To Reply
  8. 03-08-2024, 12:07 PM #8
    MaheshK5277
    MaheshK5277 is offline
    Forum Contributor
    Join Date
    12-18-2015
    Location
    Mumbai, India
    MS-Off Ver
    MS Office 365
    Posts
    136

    Re: Help with Pivot table - possibly calculated itemor field?

    Ok...got it. I was wrong when i said earlier that filter is to be applied on 'Project Status'. I now know that filter needs to be applied on 'Finish' column. i have attached a sample file showing my expectations. Please check once if this is possible.

    Thanks,
    Mahesh
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 03-11-2024, 05:49 AM #9
    MaheshK5277
    MaheshK5277 is offline
    Forum Contributor
    Join Date
    12-18-2015
    Location
    Mumbai, India
    MS-Off Ver
    MS Office 365
    Posts
    136

    Re: Help with Pivot table - possibly calculated itemor field?

    Hi Experts,

    Can someone please help with the above request?

    Thanks,
    Mahesh
    Reply With QuoteRegister To Reply
  10. 03-11-2024, 05:51 AM #10
    rorya
    rorya is offline
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent Monthly Channel / Insiders Beta
    Posts
    8,913

    Re: Help with Pivot table - possibly calculated itemor field?

    You'd have to use the data model to get counts with different date filters applied at the same time.
    Reply With QuoteRegister To Reply
  11. 03-11-2024, 06:01 AM #11
    MaheshK5277
    MaheshK5277 is offline
    Forum Contributor
    Join Date
    12-18-2015
    Location
    Mumbai, India
    MS-Off Ver
    MS Office 365
    Posts
    136

    Re: Help with Pivot table - possibly calculated itemor field?

    Ok...I haven't used Data model before. Can you help this time & i will try to apply the learnings. I have O365 & am unable to see the Data model option (my ignorance may be the cause)

    Thanks,
    Mahesh
    Reply With QuoteRegister To Reply
  12. 03-11-2024, 06:13 AM #12
    rorya
    rorya is offline
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent Monthly Channel / Insiders Beta
    Posts
    8,913

    Re: Help with Pivot table - possibly calculated itemor field?

    You access the data model via Power Pivot.

    Based on what you have described, you could do something like the attached.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  13. 03-11-2024, 08:53 AM #13
    MaheshK5277
    MaheshK5277 is offline
    Forum Contributor
    Join Date
    12-18-2015
    Location
    Mumbai, India
    MS-Off Ver
    MS Office 365
    Posts
    136

    Re: Help with Pivot table - possibly calculated itemor field?

    Thanks...exactly what i was looking for. Appreciate your support. Can you please help me with the steps to enable & achieve this?

    Mahesh
    Reply With QuoteRegister To Reply
  14. 03-11-2024, 01:17 PM #14
    rorya
    rorya is offline
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent Monthly Channel / Insiders Beta
    Posts
    8,913

    Re: Help with Pivot table - possibly calculated item or field?

    Step 1: Make the source data into a table.
    2: With a cell in the table selected, click on the Power Pivot tab, and click Add to Data Model.
    3: In the power pivot window, add a new calculated column to the right of the table using the formula:
    =FORMAT(EOMONTH(Table1[Finish],0),"mmm-yyyy")
    This will be the column used for the month slicer.
    4: Click the pivot table button in PP to add a pivot table in Excel.
    5. Right click the table in the pivot table field list and click Add Measure...
    6: Choose a name for the measure (I used CompleteCount) and input the formula:
    =COUNTROWS(FILTER(Table1,Table1[Project Status]="Complete"))
    7: Repeat step 6, adding measures for TodayLiveCount:
    =COUNTROWS(FILTER(ALL(Table1),Table1[Project Status]="Live"))
    and TodayOnHold:
    =COUNTROWS(FILTER(ALL(Table1),Table1[Project Status]="On hold"))
    then add those to the pivot table.
    I assumed from your data set that the ones flagged as Live or On Hold were current items, so there is no date filtering required there.
    Reply With QuoteRegister To Reply
  15. 03-12-2024, 01:06 AM #15
    MaheshK5277
    MaheshK5277 is offline
    Forum Contributor
    Join Date
    12-18-2015
    Location
    Mumbai, India
    MS-Off Ver
    MS Office 365
    Posts
    136

    Re: Help with Pivot table - possibly calculated item or field?

    Thanks a lot for the explanation. And you are right, Live or Hold are current items & no date filter is required for these.

    I am marking this as SOLVED. Thank you

    Mahesh
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Creating calculated field and calculated item into a pivot table
    By dvpe in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 12-12-2019, 12:15 PM
  2. Pivot Table Calculated Field - Each Item in Field B as % of Total of Field A
    By MacroPolo in forum Excel General
    Replies: 0
    Last Post: 08-15-2016, 02:07 PM
  3. Calculated Field / Column / Item in Pivot Table
    By rashid.mehmood in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 10-23-2015, 01:58 PM
  4. [SOLVED] Pivot Table - Calculated Field/Item
    By dluhut in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-19-2014, 12:46 PM
  5. [SOLVED] Calculated Item / Field in a Pivot Table
    By DPaton in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 02-17-2014, 06:32 PM
  6. [SOLVED] pivot table formulas for calculated field or calculated item
    By Vicky in forum Excel General
    Replies: 3
    Last Post: 06-06-2006, 12:10 AM
  7. [SOLVED] Pivot Table Calculated field item
    By Tim in forum Excel General
    Replies: 1
    Last Post: 09-23-2005, 08:05 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1