Forum Discussion

LostintheBIu's avatar
LostintheBIu
Helper II
6 months ago
Solved

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 o...
Need Help
Zanqueta's avatar
Zanqueta
Super User
6 months ago

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 šŸŒ€.