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:
https://postimg.cc/XGwx3YvR
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.
Any advice greatly appreciated.
Dave
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:
https://postimg.cc/XGwx3YvR
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.
Any advice greatly appreciated.
Dave
