Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
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!
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 |
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!
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |