Power Pivot / Pivot Table Problem

Dave Ford

New Member
Joined
Aug 6, 2018
Messages
1
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Forum statistics

Threads
1,223,264
Messages
6,171,081
Members
452,377
Latest member
bradfordsam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top