Forum Discussion
Getting Single Product Sales from Mixed Product
Hi
I work in a Brewery and we sell mixed cases of beer alongside single beers. I'm looking for a way to split the mixed cases and show a total sales number for the individual beers.
In Power Query Editor, I already have a query with all my individual beer sales and a separate data set for my mixed case sales.
I've tried loads of different ways to get the info, but nothing works.
Can anyone help me?
Rich
15 Replies
- lbendlin
Super User
What's the data granularity? Do you know the number of bottles per type per case?
I have made a list of all the mixed cases and which individual products go into each case. I call this the Mixed Case Key. I have tried to connect the data by linking the Mixed Cases Sales Report to the Mixed Case Key and then to the Single Products Sold Report, but that hasn't worked.
- lbendlin
If I were you I would treat this as a bottle level problem.
Please provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.Here are examples of the 2 files I'm using. The Mixed Case Key is what I was trying to use to translate mixed case sales into single beer sales.
- lbendlin
I would assume that any beer can be sold in either a "clean" case (only that beer) or in a mixed case. In that case (sorry about the pun) there is no way to distinguish/decide which scenario to apply. Do you at least have a clean/mixed flag?
All products are sold from a category. The single beers are sold under 'Bottle and Cans', the single beer cases and '12 Pack' and the mixed cases are "Mixed Cases". I use the category to filter the sales into the 2 data sets.
For working out the single beer sales, I use the category name to work out how many units are sold from each product. 'Bottle and Can' equals 1 unit and '12 Pack' equal 12 units. Then multiply the quantity sold by the units. That gives me the total number of beers sold from the single units.- lbendlin
Right, I missed that category part. That should be sufficient. I'll go back into my thinking corner, will let you know when I have something to show.
- lbendlin
If the quantity is 1 and the category is "Bottles & Cans" does that mean it is a single bottle/can, or a case of 12 identical beers?
If the category is "Bottles & Cans" it's always a single product, so 1 individual bottle or can of beer. A case of 12 identical beers is under the category "12 Pack".
- lbendlin
Here is my proposal - as mentioned earlier I would expand the transaction data to go down to bottle level. For case transactions I would explode the case contents into individual rows, for bottle transactions I would repeat the data in the row.
let Source = Excel.Workbook(File.Contents("C:\Users\xxx\Downloads\2022-10-01 Example Bookkeepping Data.xlsx"), null, true), BookKeeping_2022_11_15_1802_Sheet = Source{[Item="BookKeeping_2022_11_15_1802",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(BookKeeping_2022_11_15_1802_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Quantity", Int64.Type}, {"Date/Time", type datetime}, {"Category", type text}, {"Product", type text}, {"Device Name", type text}, {"Location Name", type text}, {"Staff", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each Date.From([#"Date/Time"]), type date), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Items", each if [Category]="Bottles & Cans" then #table({"Product","Beer Name","Units Sold"},{{[Product],[Product],1}}) else let p = [Product] in Table.SelectRows(#"Case Mix",each [Product]=p)), #"Expanded Items" = Table.ExpandTableColumn(#"Added Custom1", "Items", {"Beer Name", "Units Sold"}, {"Beer Name", "Units Sold"}), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Items",{{"Units Sold", Int64.Type}}) in #"Changed Type1"That will then give you the flexibility to look at various reporting scenarios.
Note that a new column is multiplying the transacton quantity with the detail quantity.
See attached for the pbix. I added a couple of dimension tables - they are not mandatory but may help with cutting the data a cetain way.
