Create dynamic table for cost allocation

[rodiba]

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

[v-jianboli-msft · Accepted solution]

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.

[DimaMD]

Hi @rodiba Please review the attached file and tell me if this is your desired result

[rodiba]

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