Reallocating one cost center to two different cost centers

[Suna]

I have a master data from accounting where one column is cost center. Then I have another table for explaining the data centers and another information, like locations etc. in it. We call it the key table. The same table also has another dimensions that needs to be explained so the column cost center actually has another things too. Like city codes and industry codes. But to simplify the tables are something like this:

Amount	Cost center			Cost Center	Name
150	A			A	John
400	A			B	Liz
200	B			C	Jake
50	D			D	Sarah
-70	E			E	Kate
700	B
10	C

What I would need to do is split some cost centers to two different cost centers 50/50. Lets say A needs to be divided between B and C.

I don't know where to go from here, I'm quite new in Power bi 🙂 I was thinking adding columns to the key data explaining what cost centers should be used instead of the original cost center.  Cost centers that needs to be reallocated are pretty rare (maybe 15-20 at this time) so it is not a big job to maintain this table. Like this:

Cost Center	Name	Reallocating 1	Reallocting 2
A	John	B	C
B	Liz
C	Jake
D	Sarah	E	B
E	Kate

And then some formula to make the 50 % allocating. Maybe a IF formula altough it seems a bit complicated. If Columns C and D have something, allocate the sum to these cost centers instead. Any ideas are much appreciated 🙂

[Suna]

It took me way too much time to wonder how to attach files here. And then realize it's not bossible.

Here is a link to Drive where the example data tables are

Excel data

What I am looking for is a way to divide sums for certain cost centres. So if the data has a row with the cost center J1, that sum should be allocated to 50 % to T1 and 50 % to T2. If it's J2 then 50 % to T2 and 50 % to T9 and so on. It's alwas split even to two different cost centers. For example

[v-xicai]

Hi @Suna ,

>>What I would need to do is split some cost centers to two different cost centers 50/50. Lets say A needs to be divided between B and C.

Could you clarify what's the splitting logic ?

I am not sure what desired result would you want, could you please share your sample data and desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

Please read this post to get your answer quickly: How to Get Your Question Answered Quickly.

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AnthonyTilley]

Are you able to provide a view of your data model and some sample data from each of your tables, or better a sample PBIX file.