Forum Discussion
Helper VUsing 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.
Community SupportHi JustDavid ,
Thanks for going through the PBIX and raising these questions.
To answer your questions:
1. Since your model includes multiple tiers (wfTier1, wfTier2, and wfTier3), you’ll need to apply the same Power Query approach for each level. That means:
--Creating a Tier1_Allocated table from wfTier1.
--Then using the output of Tier1_Allocated as the base input for building Tier2_Allocated from wfTier2.
--And similarly, using Tier2_Allocated to construct Tier3_Allocated from wfTier3, if required.
This ensures the correct cost flow between tiers and avoids recalculation issues.
2. Regarding the Table.ReplaceValue steps - these were added just to quickly correct some specific over-allocated values and ensure your expected totals showed up during testing. They are not part of the final solution. In your actual model, you won’t need to use these steps or manually update them for each [To] value.
Once the logic is properly set up - merging with the base table, removing duplicates to avoid over-allocation, and grouping by 'To' - Power Query will generate the correct allocation outputs automatically without any hardcoded replacements.
Hope this helps. Please reach out for further assistance.
Please consider marking the helpful reply as Accepted Solution to assist others with similar issues.
Thank you.
Helper Vv-veshwara-msft Thanks for replying.
I get the answer for my part1, and that is I need to create a PQ to accomodate the cost flow from tier to tier.
However, am not really following on your answer to my 2nd part. Thus I'd break your statement and, quote them and ask you further with questions.
You wrote "Regarding the Table.ReplaceValue steps - these were added just to quickly correct some specific over-allocated values and ensure your expected totals showed up during testing. In your actual model, you won’t need to use these steps or manually update them for each [To] value"
So I tried removing the last 2 steps of the #Replaced Value" steps. Upon removing them, the answer didn't give me the correct result as how you've done with the last 2 "#Replaced Value" steps.
You can check the screenshots below of what I did with the PQ you created by removing the last 2 steps and then highlighting the error.
To me, although you're able to replicate what I desired with the sample, but through "Replaced Value" to ensure expected totals showed up, then it's not really a viable solution that I'm looking for, as the purpose of having it done via PQ and Power BI is so that I'm taking out the manual steps process. If I had to always know in advanced what those numbers are to do the "Repalced Value" steps, it' not a manageable situation if I were to use with live data.
- v-veshwara-msft
Hi JustDavid ,
Apologies for the delay, and thanks for your patience while this was being worked on.
Just to clarify, the ReplaceValue steps in the sample Power Query were not meant to manually adjust the results to match expectations. They were only used to temporarily fix a mismatch that seemed to be caused by some extra or incorrect values in the sample data. The main idea was to avoid confusion while checking if the method works.
The actual method should work fine without any Replace steps, as long as the data is clean and doesn’t include duplicate or unexpected values. To be sure everything works correctly, I suggest testing the same steps with your own original data.
Hope this helps. Please reach out for further assistance.Thank you.
- JustDavid
v-veshwara-msft Yes, I did test with my sample data which is a mirror of my actual data but smaller size.
And like I said, I removed the last 2 PQ steps that replaced the value, and checked if DAX worked, and it didn't. Thus the replied message above to show proof.
- v-veshwara-msft
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.