Dynamic Cost Allocation Data Transformation

[LostintheBIu]

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

[ThxAlot]

[Zanqueta]

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:

1. Filter out blank/uncategorised lines (except those you have replaced with 4).
2. Group by Project and calculate:
   • Sum of amounts for Budget Line 4 (SharedCosts).
   • Sum of amounts for other budget lines.
3. Merge back to original table and create a custom column:

if [Budget Line] <> 4 then 
    [Amount] + ([Amount] / [SumOtherLines]) * [SharedCostAmount]
else 
    0

This 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 🌀.