Purchase Allocation Ratio

Hello,

I am working on a measure that will allow my company to capture purchase data. The tricky part that we are running into is that some items have cost items that are allocated to multiple items. 

For example, we will have large, medium, and small coffee and these items may contain some of the same resources such as cups, lids, straws, sleeves. 

Currently, the item (large, medium, small coffee) is a primary product ID and is a column in Table 1 while the component item ID (sleeves, lids, straws) is also a column in Table 1.

We want a purchase allocation ratio based on the number the item sold compared to the whole unit i.e. (if 1000 total coffees are sold and 400 of them are large coffee, we would like to attribute 40% of the cost of shared costs (lids, sleeves) to large coffee. Currently, the component item ID for lids or sleeves will yield the same number across all coffee sizes. 

Basically to sum it up, each item sold has a unique Primary Product ID (column). These primary product IDs have component item IDs which makes up the cost going into each item. Essentially, we need a way to sum up how many items are sold based on Primary Product IDs that share the same Component Item ID.

Thank you,