Need help calculating and converting UOM between two tables

[mdp1202]

I have one data source that provides costs in EA , CA, PL, or LB.

I have a second data source that reports issues, sometimes quantities are reported by Each, Cases, Pallets, or Pounds. 

So my first issue is that the UOM's aren't an exact match - how do I fix that?

Then, I am not sure how to cross-reference these sources. If data source 2 reports 50 cases today, it needs to pull the cost from data source 1, which may not match UOM (it may be EA).

Furthermore, the next day, data source 2 may report 8 pallets of that same item - how can I convert this to cases (or whatever the UOM is in data source 1)?

I am assuming that I need to create a data source 3, that is a table with all items by each UOM (i.e. item A: 1 pallet = 20 cases = 120 each = 240 pounds), how can I make sure the correct column gets used. 

The result I am looking for is when items are reported, the file will automatically assign a cost of failure. We have several steps in the process, so I will also be breaking down the cost by process step.

Thanks in advance for your help!

[v-shex-msft]

Hi @mdp1202 ,

Can you please share some sample data and expect the result to help us clarify your requirement? It is hard to test without any detail information.

How to Get Your Question Answered Quickly

>>Then, I am not sure how to cross-reference these sources. If data source 2 reports 50 cases today, it needs to pull the cost from data source 1, which may not match UOM (it may be EA).

Maybe you can try to create a dimension table with unique id field to link these datasources. If you mean calculate from all datasoruces, I'd like to suggest you merge these datasoruce tables on 'query editor' side.

Regards,

Xiaoxin Sheng

[mdp1202]

Attached is some sample data - the issues are pulling from one data source thru the 'Quantity' & 'UOM' columns. There is a link between data sources using 'Reference_Number' column. Data source two has 'Standard Cost' and 'UO' fields.

[v-shex-msft]

Hi @mdp1202 ,

According your snapshot, it seems like your datasources contain different format UOM values, I'd like to suggest you add a mapping table to map these different UOM values. (similar as dictionary table)
Then you can use this mapping table as bridge to link all datasources UOM fields.

Regards,

Xiaoxin Sheng