Forum Discussion
Dynamic Cost Allocation Data Transformation
Hi data ninjas,
I would be very grateful to receive some help regarding how to use Power BI for dynamic Cost Allocation.
We have Projects: A, B, C, etc.
Each project has a different number of Budget Lines: 1,2,3,4,5 & blank/uncategorised.
Each project could have any combination of these Budget Lines - a project could have all of the Budget Lines, or only 1 or 2 of them. It is a quite random mix.
For each project we need to allocate the Amounts under Budget Line 4 to the rest of the project's existing Budget Lines (if they are a number, and not blank/uncategorised/other). The amount allocated to each remaining budget line should be relatively sized to how much was originally under that Budget Line, not including the blank/uncategorised/other Budget lines.
Please see below.
Thanks in advance for the help!
Original Actuals Fact Table
| Project | Description | Budget Line | Amount |
| A | Budget 1 | 1 | 100 |
| A | Budget 2 | 2 | 50 |
| A | Budget 3 | 3 | 70 |
| A | Budget 4 | 4 | 50 |
| B | Budget 2 | 2 | 90 |
| B | Budget 4 | 4 | 110 |
| B | Shared Costs | blank | 200 |
| C | Budget 3 | 3 | 100 |
| C | Shared Costs | blank | 90 |
etc.
Intermediate step:
Apply Replace Values to 'Budget Line' column: If 'Description' column = "Shared Costs" then replace 'Budget Line' = 4
| Project | Description | Budget Line | Amount |
| A | Budget 1 | 1 | 100 |
| A | Budget 2 | 2 | 50 |
| A | Budget 3 | 3 | 70 |
| A | Budget 4 | 4 | 50 |
| B | Budget 2 | 2 | 90 |
| B | Budget 4 | 4 | 110 |
| B | Shared Costs | 4 | 200 |
| C | Budget 3 | 3 | 100 |
| C | Shared Costs | 4 | 90 |
Final Desired Outcome Table
| Project | Description | Budget Line | Amount |
| A | Budget 1 | 1 | 100 + 100/(100+50+70)*50 |
| A | Budget 2 | 2 | 50 + 50/(100+50+70)*50 |
| A | Budget 3 | 3 | 70 + 70/(100+50+70)*50 |
| A | Budget 4 | 4 | 0 |
| B | Budget 2 | 2 | 90 + 90/(90)*(110+200) |
| B | Budget 4 | 4 | 0 |
| B | Shared Costs | 4 | 0 |
| C | Budget 3 | 3 | 100+100/(100)*90 |
| C | Shared Costs | 4 | 0 |
4 Replies
Hi LostintheBIu, this scenario can be solved effectively using Power Query transformations. This is preferable if the allocation should be static and calculated during data preparation.
Steps:- Filter out blank/uncategorised lines (except those you have replaced with 4).
- Group by Project and calculate:
- Sum of amounts for Budget Line 4 (SharedCosts).
- Sum of amounts for other budget lines.
- Merge back to original table and create a custom column:
if [Budget Line] <> 4 then [Amount] + ([Amount] / [SumOtherLines]) * [SharedCostAmount] else 0This ensures proportional allocation and sets Budget Line 4 to zero.If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.
Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.
Hi LostintheBIu,
Thank you for reaching out to the Microsoft Fabric Community Forum. Also, thanks to Zanqueta, ThxAlot, for those inputs on this thread.Has your issue been resolved? If the response provided by the community member Zanqueta, ThxAlot, addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.
Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.
Thank you for using the Microsoft Community Forum.Hi LostintheBIu,
Just wanted to follow up. If the shared guidance worked for you, that’s wonderful hopefully it also helps others looking for similar answers. If there’s anything else you'd like to explore or clarify, don’t hesitate to reach out.Thank you.
