Comparing two sets of values in pivot table - stuck!

[gumblina]

Hello!

I really hope someone can help me out with this, I've been stuck on this for a while! I have a spreadsheet for tracking requests for agency worker use and the invoices. This is to allow me to compare what people ask for to what they actually use.

I've been learning about and using powerpivot and have managed to tidy up my spreadsheet and make it work much better for data input.

However, my manager wants to see the difference between requested hours and costs and invoiced hours and costs. For the life of me, I can't work out how to do it in the same pivot table!

I can't do a calculated field, because it's part of a data set (as the requests and invoices are in different tables). I've seen all the advice about duplicating a column and using 'show values as', but none of that applies as I need to compare two columns in the values section, not one set of figures to a row.

What makes it more complicated, is that the requested shift dates and invoiced shift dates might not exactly match. This doesn't entirely matter (for our purposes), so we filter the pivot table by week commencing.

I need to know the difference between hours and costs, per week commencing, for each cost centre.

I've been googling for a few hours and haven't found anything, as all the examples seem to be nice simple sales data! I'm sure there's a way to do this, but I just can't find it. Can anyone help? In case none of that makes any sense, a copy of my spreadsheet is here: https://drive.google.com/file/d/0B29U7Q6Pqcl9RVVhWUs4dXRYcXc/view?usp=sharing

Thank you!
Jemma

 

[kitjosh1050]

You can do so with multiple look-up tables. So have look-up tables like Shift, CostCentre, Date. Then have the two data sets - Requests and Costs - join to the common look-up tables.

Once you have that you should be able to subtract the measures from one another and display in the same pivot table.