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
Highlighted
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

2 REPLIES 2
Highlighted
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

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
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 290 members 3,392 guests
Please welcome our newest community members: