cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kimmend Frequent Visitor
Frequent Visitor

Allocation of general costs to products

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.

 

bild.png

 

Is this possible to solve in Query Editor or DAX?

 

Br,

Kim

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Allocation of general costs to products

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

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

4 REPLIES 4
Super User
Super User

Re: Allocation of general costs to products

Hi @kimmend

 

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

 

thx

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

kimmend Frequent Visitor
Frequent Visitor

Re: Allocation of general costs to products

Hi @LivioLanzo, hopefully this helps:

 

Starting point:

 GeneralProduct 1Product 2Product 3Total
Department A5010012090360
Department B407010100220
Department C101007080260
Total100270200270840

 

Allocation key for General costs to Products:

 Product 1Product 2Product 3
Department A33 %33 %33 %
Department B25 %35 %40 %
Department C10 %80 %

10 %

 

Desired end result:

 GeneralProduct 1Product 2Product 3Total
Department A0117137107360
Department B08024116220
Department C01017881260
Total0298239304840
Super User
Super User

Re: Allocation of general costs to products

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

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

kimmend Frequent Visitor
Frequent Visitor

Re: Allocation of general costs to products

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. Smiley Very Happy