powerpivot, combining multiple tables and calculating across them

This is cross posted to the mr excel forum here:

http://www.mrexcel.com/forum/powerpi...ross-them.html


Hi All,

I'm trying to figure out whether or not something is possible in power pivot. I'd like to load two tables into powerpivot and then set up formulas that create calculated values across the two tables.

Here's a simplified description of what the tables look like.

\1

The pivot table should join the two tables on Month and Category and allow me to create calculated values across the cost category, like this. Note it's filtered to a single cost category right now:

\1

In this view it's filtered to all cost categories but i'd be looking to still maintains the right count of members (i.e. it doesn't inflate the numbers from duplicate records as you might get if you were to join the two tables in a sql database):

\1

The actual tables I would use are quite a bit larger (too large for excel without pp), and have quite a few more of each type of category.

Would something like this be possible to do in powerpivot without having to do some data prep work in another app to join the two tables together?

Thanks for any help you can provide,

Dave