cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Samuel_Valdes Frequent Visitor
Frequent Visitor

Distribution Budget with Defined Rule

Hi,

 

I need to distribute a Budget for specific items that is made for all the Company to various Cost Centers (approx. 50). The Budget is initially located in the Cost Center called 99-Centralized and later distributed it in all the Cost Centers (e.g. 01-Sales, 02-Administration, 03-Legal, 04-Research, etc) of the company depending in a pred define distribution rule. ¿How I can achieve this using Power Query?

 

In other words, I need the following result:

 

Budget Distributed.jpg

Here a link with the example excel I show above: 

 

https://1drv.ms/u/s!Asber9wcpjf8iV3jiy57QKcUyJRE?e=enmgjQ

 

Best Regards


Samuel

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kogikoski Frequent Visitor
Frequent Visitor

Re: Distribution Budget with Defined Rule

Hi Samuel,

 

My approach would be:

 

1)  Unpivot other columns in your ditribution rules table, so you can have a table with Cost Center, Areas, and %.

2) Merge this new table with your Budget Centralized table based on the column Item.

3) Expand the percentages to create new rows. It will create multiple rows with your percentages, and will also duplicate your budget value.

4) Create a calculated column mulitplying your Budget and your percentage.

 

I didn`t try it here, but I think it should work.

 

Please let me know if that works,

 

Vagner

View solution in original post

2 REPLIES 2
Kogikoski Frequent Visitor
Frequent Visitor

Re: Distribution Budget with Defined Rule

Hi Samuel,

 

My approach would be:

 

1)  Unpivot other columns in your ditribution rules table, so you can have a table with Cost Center, Areas, and %.

2) Merge this new table with your Budget Centralized table based on the column Item.

3) Expand the percentages to create new rows. It will create multiple rows with your percentages, and will also duplicate your budget value.

4) Create a calculated column mulitplying your Budget and your percentage.

 

I didn`t try it here, but I think it should work.

 

Please let me know if that works,

 

Vagner

View solution in original post

Samuel_Valdes Frequent Visitor
Frequent Visitor

Re: Distribution Budget with Defined Rule

Vagner,

 

Thank you very much! It works really well your approach. Additionaly I put cases were the budget was not designated to the Cost Center CC 99-Centralized, but to only a specific one.

 

Here I attach a link with the solution https://1drv.ms/x/s!Asber9wcpjf8iV6qLd0CKk1zYYrb?e=tEEVZc

 

Best Regards

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)