I'm fairly new to Pivot Tables. I can build a basic one and understand some of the ins and out and do alright for most of what I need. However I'm struggling with a couple of things I'm trying to incorporate in a new table.
GOAL:
1. Create a clean looking daily report showing current days data/unit usage.
Done more or less - using a simple date filter to select a single days data, in a tabular format with blank rows for legibility.
2. Show the total to-date values for each line item
I couldn't get this to work within the pivot controls unless I'm missing something. So I'm using a 2nd pivot table to tally the totals to date with slicers linked for all the report parameters but without the date filter used on the primary table. Then I'm using a Lookup in space adjacent to the primary table based on the description column to pull the total. It works but I don't like putting formula data adjacent to a Pivot. Would prefer to do it all within Pivot functions.
Any suggestions?
3. Show all line items with any prior activity. This is really my primary issue right now. My pivot table is filtered by date for a single day. Therefore my to-date "totals" are only going to show the lines that have current days activity because that's all that gets through the date filter. How do I show ALL previously used line entries and show current days activity as a zero?
4. Here's a stumper. Some of these items have a max quantity. So I'd like to show a math function adjacent as well that has the Total available and a % used. If I can get item #3 above I can do what I did in #2 to make it work.
I'm guessing I need to add a bunch of extra dummy formula fields to my source dataset so that I have the necessary fields to display. Is that the only way to do something like this?
HEADINGS:
Date / Description / Qty Used / Project To Date Total / Original Total / % used of Original
Bookmarks