Allocating Costs

[BrucePorter]

Hi All

Hopefully someone here can help or at least point me in the right direction.

I currently produce a set of management accounts from an extracted Trial Balance (TB) which contains fields for Period, Account Code, Department, Business Unit and Amount. I've managed to create measures to show costs for the usual headings of Current Period, Same Period Prior Year, Current YTD and Prior Year YTD. I have two tables - one the TB table plus a Period table with our financial periods

There are 3 business units within the company - 2 operational units (BU1 & BU2) and a service unit (BU3) which contains departments such as Finance, IT and HR. I currently produce reports for each business unit separately but I've now been asked to show at the bottom of the operational units the allocation of the service departments. I need to apportion the costs in BU3 as a total of those BOTH directly attributible to the operational unit within each account code PLUS an allocation of the BU3 costs which is done by account code.

I've now created another linked table with the following field account code, BU1% & BU2%

So for BU1 I need to show the Finance Department total which is made up of those costs in BU3-Finance with BU1 as the Business Unit plus the allocation of the BU3 costs that go to BU1. And the same for BU2.

Hopefully this makes sense as I'm beginning to feel as if I'm

Appreciate any help anyone can give.

Bruce

 

[miguel.escobar]

can you use that linked table to create these 2 columns:
Column1: Account Code
Column2: BU that goes with each account

once you have those...just relate the account code to your fact table and when you create your new measures, all you have to do is add at FILTER at the end that should say something like
Newtable[Column2] = BU

Hope this helps!
Miguel

 

[BrucePorter]

Thanks for the response Miguel

I'm relatively new to using PowerPivot although I have been using Excel for a number of years. I'm not quite sure that I follow what you mean.

Where do you want me to create the 2 additional columns? Each account code that is used by each department in BU3 is split for example 30% to BU1 and 70% to BU2.

Unfortunately I'm at work at the moment and can't get access to post an example but will try to do later today.

Again appreciate your help.

Bruce