Combining sales quantities from a bulk item code onto a single unit item code

[zachjudge]

Hi,

I have two seperate item codes for the same exact product, one being the individual unit and the other a bulk amount (usually 10 units).

Sales come through as 1 unit sold on both items:

When a store reads this, they get confused and think we've had a lot more sales than this which is true.

I want to show these sales on the one line so the store knows exactly how many units they have sold, rather than needing to do some manipulation in excel. The order in the bulk amount but I realise it would be tricky to calculate the exact bulk amount sold because stores can often sell on more than one bulk code e.g. x50 and a x10 code which is 3 sale lines for the same product.

I have created two columns in the dim_item table that link the bulk item_code to the individual unit code and how many the bulk amount makes up of the individual unit (in this case pack_code is the individual code and the item_code is the bulk amount):

Any help would be greatly appreciated, even just pointing me in the right direction would be a huge help.

Cheers!

[MFelix · Accepted solution]

Hi @zachjudge ,

Not really getting if you want to pick up 90 units or 9 units but taking into account that you have the information for the pack code and the pack size, taking into account that the item code will have two lines one for 10 units and another with 1 unit you need to make the multiplication/division of the quantities sold by the pack size.

I made a very simple model with Items and sales the tables are related by the pack_code.

I added the following measure:

TotalSales = 
SUMX (
    ADDCOLUMNS (
        SUMMARIZE ( Sales, Items[pack_code], "Sales", SUM ( Sales[Quantity] ) ),
        "PackSize", CALCULATE ( SELECTEDVALUE ( Items[packsize] ) )
    ),
    [Sales] * [PackSize]
)

Has you can see below the calculation is made line by line in the correct way when you have a grouping the calculation continues correct:

Check PBIX file attach.