Forum Discussion
Create dynamic table for cost allocation
Good morning,
We have users managing files and we would like, for accounting purposes, to calculate the analytic cost associated to any file, distributing the cost of any user over all the files he is managing.
I have a table containing costs per user, with a specific date. I would like to distribute those costs equally on all the files the user is managing, keeping the reference to that specific date.
Data is organised as follows.
I have a table with the costs per user (table A), referred to a specific date:
| Date | User | Cost |
| 01/01/2023 | Bob | 100 |
| 03/01/2023 | Tom | 60 |
In another table (table B), I have the list of files assigned to the users:
| File | Assigned to |
| 1 | Bob |
| 2 | Bob |
| 3 | Bob |
| 4 | Bob |
| 5 | Tom |
| 6 | Tom |
| 7 | Tom |
| 8 | Tom |
| 9 | Tom |
| 10 | Tom |
I would like to calculate a table containing the costs distributed per file and date. The result should be:
| Date | File | Cost per file | Note |
| 01/01/2023 | 1 | 25 | Bob's cost divided by the number of assigned files (=4) |
| 01/01/2023 | 2 | 25 | |
| 01/01/2023 | 3 | 25 | |
| 01/01/2023 | 4 | 25 | |
| 03/01/2023 | 5 | 10 | Tom's cost divided by the number of assigned files (=6) |
| 03/01/2023 | 6 | 10 | |
| 03/01/2023 | 7 | 10 | |
| 03/01/2023 | 8 | 10 | |
| 03/01/2023 | 9 | 10 | |
| 03/01/2023 | 10 | 10 |
Thanks in adavnce for your help.
Roberto
Hi rodiba ,
Please try:
Duplicate Table A
Then Append Queries(Table A left outer join Table B)
Expand Table B
Add custom column
remove other columns
Here is the M code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyNjJR0lp/wkIGloYKAUqwOUMkaSCsnPBZJmUBlTDE0gmVgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, User = _t, Cost = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"User", type text}, {"Cost", Int64.Type}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"User"}, #"Table B", {"Assigned to"}, "Table B", JoinKind.LeftOuter), #"Expanded Table B" = Table.ExpandTableColumn(#"Merged Queries", "Table B", {"File", "Assigned to"}, {"Table B.File", "Table B.Assigned to"}), #"Added Custom" = Table.AddColumn(#"Expanded Table B", "Custom", each [Cost]/ Table.RowCount( Table.SelectRows(#"Table B",(x)=>x [Assigned to]=[User]))), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Cost", "Table B.Assigned to"}) in #"Removed Columns"Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi DimaMD ,
thanks for your response. It works for my simplified data, but doesn't work anymore if Table A has multiple rows for a user on a different date, that can be a possibile situation.
If, for example Bob has another cost (red line below), result table shold have more extra lines.Date User Cost 01/01/2023 Bob 100 03/01/2023 Tom 60 05/01/2023 Bob 60 Result shoud be:
Date File Cost per file Note 01/01/2023 1 25 Bob's cost divided by the number of assigned files (=4) 01/01/2023 2 25 01/01/2023 3 25 01/01/2023 4 25 03/01/2023 5 10 Tom's cost divided by the number of assigned files (=6) 03/01/2023 6 10 03/01/2023 7 10 03/01/2023 8 10 03/01/2023 9 10 03/01/2023 10 10 05/01/2023 1 15 05/01/2023 2 15 05/01/2023 3 15 05/01/2023 4 15
Hi rodiba ,
Please try:
Duplicate Table A
Then Append Queries(Table A left outer join Table B)
Expand Table B
Add custom column
remove other columns
Here is the M code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyNjJR0lp/wkIGloYKAUqwOUMkaSCsnPBZJmUBlTDE0gmVgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, User = _t, Cost = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"User", type text}, {"Cost", Int64.Type}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"User"}, #"Table B", {"Assigned to"}, "Table B", JoinKind.LeftOuter), #"Expanded Table B" = Table.ExpandTableColumn(#"Merged Queries", "Table B", {"File", "Assigned to"}, {"Table B.File", "Table B.Assigned to"}), #"Added Custom" = Table.AddColumn(#"Expanded Table B", "Custom", each [Cost]/ Table.RowCount( Table.SelectRows(#"Table B",(x)=>x [Assigned to]=[User]))), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Cost", "Table B.Assigned to"}) in #"Removed Columns"Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
