Using VAR "calculated measure" INSIDE CALCULATE perhaps? Allocation from Tier to Tier

Hi JustDavid ,

Thanks again for your follow-up and for confirming that your sample data mirrors your actual setup.

Here is the updated version which works without needing any ReplaceValue steps:

let
    Source = Table.NestedJoin(wfTier1, {"From", "PBR Period"}, factTable, {"Business Unit", "FY_Period"}, "factTable", JoinKind.LeftOuter),
    #"Expanded factTable" = Table.ExpandTableColumn(Source, "factTable", {"Amount"}, {"Amount"}),
    #"Removed Duplicates" = Table.Distinct(#"Expanded factTable", {"From", "To", "PBR Period", "Amount"}),
    #"Added AllocValue" = Table.AddColumn(#"Removed Duplicates", "AllocValue", each [Amount] * [Alloc]),
    #"Grouped Rows" = Table.Group(#"Added AllocValue", {"To", "PBR Period"}, {{"TotalAllocated", each List.Sum([AllocValue]), type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each true),
    #"Removed Duplicates1" = Table.Distinct(#"Filtered Rows", {"To"})
in
    #"Removed Duplicates1"

The key change was ensuring the join happens on both From and PBR Period columns, which properly aligns the allocation logic period-wise. Additionally, removing duplicates based on From, To, PBR Period, and Amount prevents repeated cost entries from inflating the totals - which was the main cause of the earlier over-allocated results. So with these changes, the Tier 1 allocations now correctly match expectations without needing any manual adjustments.

Thanks again for your patience while this was being worked.

Hope this helps, and please reach out for further assistance.

If this post helps, then please consider to Accept as the solution to help the other members find it more quickly.

Thank you.

Please find attached .pbix for reference.