Forum Discussion

Rich_Webb's avatar
Rich_Webb
Frequent Visitor
3 years ago

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 ...
Need Help
Rich_Webb's avatar
Rich_Webb
Frequent Visitor
3 years ago

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's avatar
    lbendlin
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      Rich_Webb
      Frequent Visitor
      3 years ago

      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's avatar
        lbendlin
        Icon for Super User rankSuper User
        3 years ago

        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?