Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Here a link with the example excel I show above:
https://1drv.ms/u/s!Asber9wcpjf8iV3jiy57QKcUyJRE?e=enmgjQ
Best Regards
Samuel
Solved! Go to Solution.
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |