Excel Pivot Table Totals Dates Incorrectly

[dmazimoto]

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).

[XLent]

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

[XLent]

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.

[dmazimoto]

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