- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Community Information
- Community Accounts & Registration
- Using the Community
- Community Feedback
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Allocation of general costs to products

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

kimmend

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-28-2019
10:13 AM

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.

1 ACCEPTED SOLUTION

Accepted Solutions

LivioLanzo

Super User

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-28-2019
01:07 PM

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

LivioLanzo

Super User

Re: Allocation of general costs to products

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-28-2019
11:23 AM

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

Re: Allocation of general costs to products

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-28-2019
11:53 AM

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

Super User

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-28-2019
01:07 PM

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

Re: Allocation of general costs to products

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-29-2019
12:49 AM

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.