Calculate Cost per Unit - Split String?

[#1]

Hello,

I am using the Northwind Database (Microsoft) as an example and i am facing the following issue:

I want to calculate the Profit Margin. I do have the Sale Price per Unit. When it comes to the Cost per Unit,

the amount is for a whole box. To make it more clear i ll give an example:

Sales in Retail

Product                    Quantity              Price

Coca Cola Can              1                  $1.55

Procurement (wholesale from the distributor)

Product                                      Quantity                  Price

24 cans Coca Cola (box)                 1                        $22.00

There is inconsistency between buying from the wholesaler and selling retail.

In other words, I would like to distinguish somehow the number of products in the box(24) and then use it

to find the Cost per Can.

Please help!!!! Thank you

[albertovarela]

Do you have a field with units per box?

[#3]

Well yes, but it contains other info as well.

Here are a few records (Exactly as they appear in the database)

QuantityPerUnit          UnitPrice

10 boxes x 20 bags	18.0000
24 - 12 oz bottles	19.0000
12 - 550 ml bottles	10.0000
48 - 6 oz jars	22.0000
36 boxes	21.3500
12 - 8 oz jars	25.0000
12 - 1 lb pkgs.	30.0000
12 - 12 oz jars	40.0000
18 - 500 g pkgs.	97.0000

Hope my problem is more clear now

[albertovarela]

I get it now. It's not consistent at all!... it won't be easy if you can't find a way to determine the conversion between wholesale and retail. Sorry for not being very helpful...

[#5]

Believe it or not this is a real world scenario (according to Microsoft) and it seems very challenging. Lets wait and see if someone has an idea. There must be a way. Thank you for your help anyways