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

[TomMartens]

Hey,

please provide a pbix file that contains sample data, upload the sample data to onedrive or dropbox and share the link.

Regards,

Tom

[Anonymous]

Hello Tom,

here you are. I made this model simple, but obviously it is a little more complexe when doing this with many more accounts and companies, thats why I wanted to find a dynamic way to do this reallocation using parameter tables and not needing to type every single different re-alllocation criteria in DAX.

Thanks for you help

Regards

Mark

https://onedrive.live.com/?id=E9FCF526ED62CB9A%21704&cid=E9FCF526ED62CB9A

[TomMartens]

Hey,

unfortunately the link to the pbix file seems no longer valid. Can you provide a link that is not time bombed or expires and if possible make sure that it's not necessary to login.

Regards,

Tom

[Anonymous]

This should work.

Mark

https://1drv.ms/f/s!AprLYu0m9fzphUDTdDFPVPuq_j-Q

[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]

After some adapations to my more complexe data stucture it work perfectly well!

Thanks again for you help

Regards

Mark

[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

[TomMartens · Accepted solution]

Hey Mark,

if my post solves your problem, please mark the post as answer, this will help others. If you find my post special, don't hesitate to give kudos 🙂

Regards,

Tom 

[itchyeyeballs]

It looks like you may be better doing this during the data load in powerquery, assuming the ratios are fixed this should be more efficent than using DAX and easier to achieve using the merge table & calculated column functionality.

[Anonymous]

Yes I thought about this possibility but its didn't seems the more efficent one in terms of report size and automation.

I would defintely try it out if I can't resolve it with DAX.

Regards

Mark