UOM Conversion Rates

[FuzzyBI]

Hi All, need your expertise.
The attached image is both a fact table about sales/return and a fact table about conversion rate

The green colour column is the desired result. To further elaborate
Cust1001 only purchased in bottle therefore, the conversion rate from the conversion table doesn't apply which is 6.
In another word, Cust1001 doesn't need to convert to BOX or CTN if the company didn't buy any.

Cust1002 purchase in both BTL and BOX.
If we receive any return under BTL, we assume it's from BOX. Hence, the conversion rate is 6.
Secondly, the conversion for box is 1 because Cust1002 didn't purchase any CTN. Hence, we don't need to convert to CTN.

Cust1003 purchase in both BOX and CTN.
Similar situation with Cust1002, we assume any BOX return is from CTN. Hence the conversion rate is 5.

All sales conversion rate will be 1 because we don't need to do any conversion for sales

Please help me. It can be in calculated column or measure.
If possible, provide both.

Thank you in advance.

P.S. Do you guys know how to upload excel/pbi file for easier support? I wish to upload my sample file for easier/simpler support from you all.

[Greg_Deckler]

@FuzzyBI Maybe something like this:

Conversion Rate Column =
  VAR __Cust = [CustomerCode]
  VAR __Item = [ItemCode]
  VAR __UOM = [UOM]
  VAR __Count = COUNTROWS(SUMMARIZE(FILTER('Table',[CustomerCode] = __Cust),[UOM])))
  VAR __Result =
    IF( __Count < 2, 1, MAXX(FILTER('Table2', [Item code (sub)] = __Item && [UOM (sub)] = __UOM),[Conversion])
RETURN
  __Result