Getting Single Product Sales from Mixed Product

[Rich_Webb]

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

[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?

[Rich_Webb]

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.

[Rich_Webb]

That's great. It looks like you've solved it but I can't figure out how you exploded the mixed cases in the transactions. I've tried copying your steps but it's not working for me.
Any chance you could give me some advice on exploding cases?

[lbendlin]

I love the innuendo this topic produces!  I am also concerned about  getting thirsty...

Anyway - here is the main piece:

#"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)
  )

If the category is "Bottles & Cans"  then I create a fake table that lists the beer name both as the product and the beer name, and sets the quantity to 1.  However, if the category is different then I am doing a lookup on the Case Mix table based on the Product name.  That will pull in all the rows for that case, including the participating beers, and their quantity.

The next step then brings that into a usable format by expanding to new rows where needed.

[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?

[Rich_Webb]

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.

[Rich_Webb]

Great, thanks for looking into this for me. Your help is appreciated.

[Rich_Webb]

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.

[Rich_Webb]

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.

Example Mixed Case Key 

Example Bookkeeping 

[lbendlin]

Thank you for providing sample data.  I see there is a conflict in the meaning of the "Product"  field. In the Case Mix table "Product"  is identifying the case type but in the transaction/bookkeeping data "Product" identifies the individual beer.  Can you please clarify?

[Rich_Webb]

That'll be because the 2 lots of data come from the same master report. When we sell anything it goes under product sales.
I've duplicated our main bookkeeping report and filtered 1 by single products and the other by mixed cases.
My idea was this would give me 1 report to show single beer sales and a second that I would need to convert to single products and relate back to the single products to give me 1 report with all product sales.

[lbendlin]

What's the data granularity?  Do you know the number of bottles per type per case?