Allocation of general costs to products

[kimmend]

Hello,

I have three products that get costs and revenue from three different departments. Some costs are not bookable straight to the products, but end up in a "general" column.

To split the costs from the general column into the products, an allocation key has been defined per product. This key is different for each department, but the general column is always emptied, and everything should be allocated to the products.

See a simplified example below.

Is this possible to solve in Query Editor or DAX?

Br,

Kim

[LivioLanzo]

Hi @kimmend

could you post your original data set in a copyable format?

thx

[kimmend]

Hi @LivioLanzo, hopefully this helps:

Starting point:

	General	Product 1	Product 2	Product 3	Total
Department A	50	100	120	90	360
Department B	40	70	10	100	220
Department C	10	100	70	80	260
Total	100	270	200	270	840

Allocation key for General costs to Products:

	Product 1	Product 2	Product 3
Department A	33 %	33 %	33 %
Department B	25 %	35 %	40 %
Department C	10 %	80 %	10 %

Desired end result:

	General	Product 1	Product 2	Product 3	Total
Department A	0	117	137	107	360
Department B	0	80	24	116	220
Department C	0	101	78	81	260
Total	0	298	239	304	840

[LivioLanzo · Accepted solution]

You can apply some trasformations within Power Query to get to the right format:

First load the Allocations table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckktSCwqyU3NK1FwVNJRMjZWRSFjdVCUOAGFjUzBkmDSxABDiTNQ2NAAJGkBJkHs2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Department = _t, #"Product 1" = _t, #"Product 2" = _t, #"Product 3" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Department", type text}, {"Product 1", Percentage.Type}, {"Product 2", Percentage.Type}, {"Product 3", Percentage.Type}}),
    UnpivotedOtherColumns = Table.UnpivotOtherColumns(ChangedType, {"Department"}, "Product", "Percentage")
in
    UnpivotedOtherColumns

then load the Sales table without General records:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckktSCwqyU3NK1FwVNJRMjUAEoYGYNIIRFoaKMXqoChzAoqagKTMIWqhGtBUOSPLQdVaAFXFAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Department = _t, General = _t, #"Product 1" = _t, #"Product 2" = _t, #"Product 3" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Department", type text}, {"General", Int64.Type}, {"Product 1", Int64.Type}, {"Product 2", Int64.Type}, {"Product 3", Int64.Type}}),
    UnpivotedOtherColumns = Table.UnpivotOtherColumns(ChangedType, {"Department"}, "Product", "Amount"),
    FilteredRows = Table.SelectRows(UnpivotedOtherColumns, each ([Product] <> "General"))
in
    FilteredRows

and this will get you to the final Sales that you can load to your model, you do not need to load the previous 2 to the data model:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckktSCwqyU3NK1FwVNJRMjUAEoYGYNIIRFoaKMXqoChzAoqagKTMIWqhGtBUOSPLQdVaAFXFAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Department = _t, General = _t, #"Product 1" = _t, #"Product 2" = _t, #"Product 3" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Department", type text}, {"General", Int64.Type}, {"Product 1", Int64.Type}, {"Product 2", Int64.Type}, {"Product 3", Int64.Type}}),
    UnpivotedOtherColumns = Table.UnpivotOtherColumns(ChangedType, {"Department"}, "Product", "Amount"),
    FilteredRows = Table.SelectRows(UnpivotedOtherColumns, each ([Product] = "General")),
    MergedQueries = Table.NestedJoin(FilteredRows,{"Department"},Allocations,{"Department"},"Allocations",JoinKind.Inner),
    ExpandedAllocations = Table.ExpandTableColumn(MergedQueries, "Allocations", {"Product", "Percentage"}, {"Product.1", "Percentage"}),
    InsertedMultiplication = Table.AddColumn(ExpandedAllocations, "Multiplication", each [Amount] * [Percentage], type number),
    RemovedOtherColumns = Table.SelectColumns(InsertedMultiplication,{"Department", "Product.1", "Multiplication"}),
    RenamedColumns = Table.RenameColumns(RemovedOtherColumns,{{"Product.1", "Product"}, {"Multiplication", "Amount"}}),
    AppendedQuery = Table.Combine({RenamedColumns, SalesProdcuts})
in
    AppendedQuery

[kimmend]

Thanks @LivioLanzo, this works! I simulated the result for the simplified test case, now I just need to test it on the real life case.