Re-allocating costs from one cost center to multiple cost centers

[Anonymous]

Hello Power Bi community, I need your help! 🙂

context: cost re-allocation for certain cost centers into determined cost center multiplying by the correspondant given ratio. (total = 100% re-allocation)

This table is showing entries for the account 65130 and is dispatch in different cost centers (offices) FLY441, FNI441, FPS441,... but some entries are also in the regional cost center FR1441. So what needs to be done is reallocating every FR1441 entries to each offices cost centers by a given ratio.

I m using parameter tables such has the accounts and cost centers where to cost should be dispatch in with the % of allocation.

And a parameter table that tells every cost centers and accounts that need to be re-allocated.

I've tried many DAX formula and none are working. Basically what I m trying to do in DAX is IF in the cost center you see in the entries table matches with the cost center table to re-allocate && it should match by accounts aswell

return value if true Switch1st actuals amount for the cost Office cost centers (FLY441, FLI441,...) * ratio attributed to that cost centers and account + the actuals initial entrie amount. Switch2nd actuals entries for cost center FR1441 * 0. (because we don't anything left in this entries since it should be reallocated), if false then simply keep the actual amount.

I used add columns to complete to do the testing.

I hope you'll be able to help me with that 🙂

Best regards

Mark

[TomMartens · Accepted solution]

Hey,

for a couple of reasons I would not recommend to use DAX, this is basically for this reasons:

• the level of granularity in your "base" table makes the DAX very complex
• DAX is not able to create rows, basically this makes it impossible to allocate a value if the target center with the appropriate detail already exists in your table

Instead I would use a combination of Merge and Append transformations in PowerQuery as @itchyeyeballs already mentioned.

Here you will find a pbix file that contains a very simplified model of your data.

In addition to the existing columns I created a custom column "type of allocation" with the value "base" in the base table. The base table also contains a column something that represents all the existing other columns like date and label columns. 

1 merge tables source and target

The query "merge soure and target" merges (the result has more columns) the tables "source" and "target".

The table "source" looks like this

The table "target" looks like this

using these merge settings

i get this result after expanding the table

2 Merge base table and the table from the previous step

I use the table from the previous step as input for a merge (Merge as new) with the base table using these merge settings

after expanding the table i get this result

As you can see, each row from the base table (Account: 65130 | Center:FR1441) is spreaded to the 7 target centers.

Finally I added a custom column "type of allocation" with the value "allocation" and another column "Amount" that multplies the amount from the base table (65130 | FR1441) with the value from the column share.

Then I removed all the columns that are not available in the base table.

I renamed the query to "allocation".

3 append the tables base and allocation

The last step is to append both tables "base" and "allocation", I also used "Append as new".

Hopefully this provides you an idea!

In this example I used "Merge as new" and "Append as new" just for demonstration, in real life I would recommend to use the  Merge and Append transformations to add the 2nd table to the selected table.

Regards,

Tom

[Anonymous · Accepted solution]

OMG I would have expected so much from you :-))

Thanks a lot!!

I don't know what to say THANKS.

Yes I tought that creating lines (new content) was going to be very difficult.

Thanks again,

Have a nice week end 🙂

Mark