+ Reply to Thread
Results 1 to 4 of 4

Excel Pivot Table Totals Dates Incorrectly

  1. #1
    Registered User
    Join Date
    05-04-2020
    Location
    Texas
    MS-Off Ver
    365
    Posts
    2

    Angry Excel Pivot Table Totals Dates Incorrectly

    Good afternoon all. I am on day 3 of searching the interwebs, calling my Excel guru friends and I will have not been able to figure this out, to top it all I'm on a pretty short deadline for the end product which I have yet to produce.

    I want to apologize in advance if the answer has been covered in these forums maybe using different words but I myself could not find the answer and thus ask for your help.

    I have a data table with plan dates and actual dates. The plan dates do not adjust. When I add the data to a pivot the totals get adjusted in a weird way. For example, there are 10 projects with January plan dates, and 16 total actual. When filtering to January (via pivot filter or graph slicer) the plan adjusts to 16 making it impossible to show when the total plan was overachieved. The plan should be 10 not 16 and for the life of me I cannot figure out why this is happening. I included a sample of the data I am looking for, the data table, and what I currently get using a pivot. I need the ability to present this via graph/slicer for easier maneuverability.

    Thank you in advance.

    PS. This "weird" behavior is also happening when counting actuals (actuals get counted that do not exist).
    Attached Files Attached Files

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    1,888

    Re: Excel Pivot Table Totals Dates Incorrectly

    Well, the Pivot fields are independent of one another and, in terms of your criteria, mutually exclusive.

    If you filter on specific Actual dates then only those records/rows are available for analysis in the Pivot.

    Given above, it follows that if you filter on your Jan Actual dates there are 16 Rows of data in your table, each has a Plan date, ergo Count of Plan = 16
    Further, of your 10 Jan Plan dates only 4 would be included in those 16 rows, the other 6 have a non-Jan Actual date and thus 'out of scope'

    You could, in principle, split your Table into two and generate your Chart c/o a Multi Consolidation Pivot, but Slicer won't persist (unfortunately) -- refer attached Sheet1

    edit:
    a simpler alternative again would be to have all dates stored in a solitary column with "type" [Plan/Actual] added in a new column -- use Type as Column Label, and set Slicer on single Date field
    Attached Files Attached Files
    Last edited by XLent; 05-05-2020 at 05:17 AM. Reason: correction: slicer won't persist with MC PT

  3. #3
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    1,888

    Re: Excel Pivot Table Totals Dates Incorrectly

    In retrospect, given your version, it would be "cleaner" to use Power Query to transform the underlying data (for use in Pivot) whilst leaving your source data as-is.

    As you add data to your source Table you can click Refresh on the Pivot to get latest.

    See attached.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    05-04-2020
    Location
    Texas
    MS-Off Ver
    365
    Posts
    2

    Red face Re: Excel Pivot Table Totals Dates Incorrectly

    Thank you very much XLent. The second response is exactly what I am looking for since the slicer is needed.

+ 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 Showing Time Incorrectly
    By King of Ka in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 12-17-2019, 10:00 AM
  2. Calculating weekly totals by dates from a pivot table
    By Craig_Foggo in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 05-08-2019, 07:42 AM
  3. Pivot Tables Incorrectly Sorting Dates
    By Phronesis in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 04-06-2016, 03:49 PM
  4. VBA - Created Pivot Table - Filters Incorrectly
    By jhuang5132 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-13-2014, 05:02 PM
  5. Pivot Table Calculating Incorrectly?
    By orcsab in forum Excel General
    Replies: 1
    Last Post: 03-16-2012, 02:09 AM
  6. Grouping Dates into Monthly Totals - Pivot Table.
    By samprince in forum Excel General
    Replies: 3
    Last Post: 10-11-2006, 09:32 AM
  7. Pivot Table Totals Incorrectly Summed
    By Ms MIS in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-12-2006, 01:30 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