SUMIFS in Pivot Table

[jsmilke]

All,

I have two tables. The first one lists the weekly requirement for specific items in specific weeks. See the example below:

Week Item Weekly Requirement
24 1 100
24 2 200
24 3 300
25 1 110
25 2 150
25 3 330

The other table lists what has actually been produced for specific items in specific weeks and on specific days. See the example below:

Week Day Item Daily Actual
24 13-Jun 1 19
24 13-Jun 2 6
24 13-Jun 3 16
24 14-Jun 1 3
24 14-Jun 2 18
24 14-Jun 3 16

I've made a pivot table out of the second table so that data can easily be examined for specific weeks and items at the daily level. I would like to add the 'Weekly Requirement' from the first table for each item and display the sum of 'Daily Actual' for the week as a percentage of the 'Weekly Requirement' for the first table. How can I do this? I was thinking of a calculated field using SumIfs in the pivot table, but I can seem to get that to work. Any ideas are much appreciated!

Thanks in advance, I've also attached some sample data.

[xlnitwit]

Hi,

Do you have Power Pivot available to you?

[jsmilke]

Yes, I have access.

[xlnitwit]

It is a bit of a longwinded process but here are the basic steps:

1. Make your two data tables into actual Tables. (I named them tWeekly and tDaily)
2. Load them into Power Pivot as linked tables.
3. Create a new calculated column in each table using the formula: =[Week]&"|"&[Item]
4. Create a relationship between the two tables using the new calculated column.
5. Create a new calculated column called Weekly_Req in the daily data table using the formula: =related(tWeekly[Weekly Requirement])
6. Create your pivot table.
7. Add a new measure to the tDaily table using: =AVERAGE(tDaily[Weekly_Req])
8. Lay out the pivot table with Item in the Row area and Week and Day in the column area, then add Daily Actual and Weekly_Req to the data area.
9. You will now have the Weekly_Req repeated for each day. To get rid of that you need to create a column-based set and remove the daily entries for Weekly_Req.

At this point your pivot table will resemble the attached.

To be honest, if the final layout is as simple as the pivot table in your original example, where you only show one week's data, you may as well use a regular pivot table and add the Weekly_Req to your daily table using VLOOKUP or INDEX/MATCH and then just add that as a row field next to the Item field.