Power Pivot / Pivot Tables Problem|Power Pivot|Excel Forum|My Online Training Hub

Hello All

My first post - usually, when I have a problem, I keep searching the Internet until I can figure the answer. This time I've come to a dead end and need help

As background I've been using spreadsheets since before Excel came on the scene and am comfortable, though by no means an expert, with data normalisation. However I've only recently started using Power Pivot

I've created a PP data model to represent the costs involved in a project, with a basic structure as follows:

The lowest level is the Cost Item, held in tblCostItems. Each Cost Item belongs to a SubActivity, held in tblSubActivities.

[tblCostItems]SubAct is linked to [tblSubActivities]SubActID

Each SubActivity belongs to both an Activity and to a Partner. There are higher level links that are not relevant to this problem.

[tblSubActivities]Activity is linked to [tblActivities]ActivityID
[tblSubActivities]Partner is linked to [tblPartners]PartnerID

For each Cost Item there's a Current Budget. In pivot tables, Current Budget sums perfectly according to SubActivity, Activity and Partner.

There's a separate table that lists expenditure. Ideally, Expenditure would be recorded against Cost Item, but this is not possible. Instead, Expenditure is recorded against SubActivity. For each record, the project month (an integer from 1 - 36) in which the expenditure occurred, is recorded in field ExpMonth

[tblExpenditure]ExpSubAct links to [tblSubActivities]SubActID
[tblExpenditure]ExpMonth links to [tblMonths]Month

The pivot table showing Expenditure by Month (SubActivities as rows, ExpMonth as columns) works fine. What I want to do is add a final column to the Expenditure by Month PT, which shows the Current Budget for the SubActivity. Something like this:

SubActivity1 Month1Exp.1 Month2Exp.1 .... Month36Exp.1 GrandTotalExp1 CurrentBudget1
SubActivity2 Month1Exp.2 Month2Exp.2 .... Month36Exp.2 GrandTotalExp2 CurrentBudget2

...so we can see expenditure by month, total expenditure to date and Current Budget for each activity. But I can't make this happen. The PT shows me the correct monthly expenditure phasing, but Current Budget is repeated 36 times. I can (sort of) understand why it's happening, but just can't figure a way around it.

Of course I can hide the unwanted columns, but that's not tackling the underlying problem (and the OCD side of me would NOT be happy!)

The complete workbook is too large to attach, but can be accessed here.  My attempts at building the PT are on sheets PT1 and PT2.

Any advice greatly appreciated.

Dave