Log In
Register
Home
Active Member
May 27, 2020
Offline
Hi
I am trying to build a cost allocation model. The idea is relatively simple. I have amounts on cost centres and allocate them to other cost centres. The issue is that the allocation is cascading i.e. cost centre 1 needs to be allocated then cost centre 2 (including the share of cost centre 1) needs to be allocated.
Here is where I am struggling as a newbie: There are not that many cost centres and I could do it with queries one by one. But this will still create a bunch of queries and I imagine there must be a smarter way or technique to do it.
Question: has anyone already done something like this or can recommend a smart approach?
Many thanks in advance.
Franz
See Answer
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Franz,
I'm a newbie in cascading costs, so it will be important for me to see a sample of the source data and how the result should be.
Please upload a file with source data and a manual example of the desired result.
Thank you
Active Member
May 27, 2020
Offline
Hi Catalin
I attached a sample for what I am trying to do.
Currently I applied a solution by calculating the cost allocation with formulas in excel directly. But I am sure there must be a better way in power query.
Any idea how to improve would be highly appreciated.
Cheers
Franz
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Franz,
That should not be called "Cascading cost", it's tornading costs... Almost twisted my brains.
I had to build a recursive function to get the results: (this is the "Convert" function, used in the main query)
(OrigTable,Allocations,a,b,lst)=>
let
//get conversion percentage
Key=(k1,k2)=>
let
FilteredRows = try Table.SelectRows(Allocations, each ([From] = k2) and ([To] = k1))[Key]{0} otherwise 0
in
FilteredRows,
//get the original amount
Orig=(CC as number) as number=>
let
Result=Table.SelectRows(OrigTable, each _[CC]=CC)
in
Result[Amount]{0},
Result = if List.IsEmpty(lst) then
0
else
List.Accumulate(lst,0,(state,current)=>state+Convert(OrigTable,Allocations,b,current,List.Select(List.Distinct(Allocations[From]), each _ < current ) ) )
in
Key(a,b) * (Orig(b) + Result)
And here is the main query:
let
Source = Excel.CurrentWorkbook(){[Name="BaseData"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Period", Int64.Type}, {"CC", Int64.Type}, {"Amount", type number}}),
RemovedColumns = Table.RemoveColumns(#"Changed Type",{"Period"}),
Allocation=Excel.CurrentWorkbook(){[Name="Allocation"]}[Content],
AllocationChanged = Table.TransformColumnTypes(Allocation,{{"Sequence", Int64.Type}, {"From", Int64.Type}, {"To", Int64.Type}, {"Account", Int64.Type}, {"Account desc.", type text}, {"Key", Percentage.Type}}),
#"Added Custom" = Table.AddColumn(RemovedColumns, "Convert To", each List.Distinct(AllocationChanged[From])),
#"Expanded Columns" = Table.ExpandListColumn(#"Added Custom", "Convert To"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Columns",{{"Convert To", Int64.Type}}),
#"Added Custom2" = Table.AddColumn(#"Changed Type1", "Custom.1", (x)=> Convert(RemovedColumns, AllocationChanged,x[CC],x[Convert To],List.Select(List.Distinct(AllocationChanged[From]), each _ < x[Convert To] ))),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom2",{{"Custom.1", type number}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type2", "Allocation", each if [Custom.1]>0 then [Custom.1] else 0),
#"Added Custom3" = Table.AddColumn(#"Added Custom1", "Release", each if [Custom.1]<0 then [Custom.1] else 0),
#"Changed Type3" = Table.TransformColumnTypes(#"Added Custom3",{{"Allocation", type number}, {"Release", type number}})
in
#"Changed Type3"
Active Member
May 27, 2020
Offline
Hi Catalin
I don't know what to say. Putting so much work into my problem is very generous.
Thanks a million for the attachment. When I tried to use your code directly an error popped up on the CONVERT function. But the attached workbook works fine.
To be honest: Your solution is quite a challenge for me. I don't fully understand it yet ... but I will study it thoroughly (which will certainly keep me quite busy).
Again, many many thanks.
Best regards
Franz
Answers Post
1 Guest(s)
