Hello all

Not sure where I should be posting this question but I think Power Pivot is appropriate. I'm possibly jumping the gun here as I haven't completed Mynda's Power Pivot course but it's a spreadsheet I'm trying to create for work and have been trying for the last week to figure out.

I have two sets of data. One which is at the transactional level that has around 220,000 rows of data. I've use PQ to clean and transform data and loaded directly into the data model. I've then created lookup tables that I have also loaded into the data model and created the reports I want. So far so good.

However, there is also budget data which is at the GL code level. This data actually contains all the detail I need for my reports (it has budget data and actuals which can be used in reports) except that with the transactional data, I am able to group my actual spend by month and quarter and with the PQ set up, I can update as frequently as I download the data.

My questions is this, would it be possible to create a report that combines the the budget data at GL code level with the transactional data? I've created a workbook that replicates what I'm trying to do for work. There's a tab 'spend summary budget' which combines the two different data sets. I can get the budget data in there and the actuals but only if I use the GL code (a column from budget data) as a row in the pivot table. If I try and put the summary detail or location fields (from the lookup tables) into the Pivot table, I just get the total budget sum for every line item.

I suspect this is not possible but just wanted to confirm to be sure. If not, what would be another way of combining transactional data with data at GL code level? Please find attached sample excel worksheet which has relationships created in Power Pivot. Thank you for your time.

Tim