Creating a weight/cost ratio measure

[Anonymous]

Hi

I'm pretty awful with measures, so while I don't think this is particularly difficult I'm struggling to write it out. I have data across two tables, Orders and Shipments. A shipment is made up of one or more orders, so in this example Orders A and B make up Shipment 0001.

Orders	Weight
Order A	25
Order B	30

Shipments	Total Weight	Total Cost (USD)
Shipment 0001	55	200

From this I need to find the cost per order:

Shipments[Total Cost] / Shipments[Total Weight] * Orders[Weight]

Order A Cost = 200 / 55 * 25 = $90.91

Many thanks!

[V-lianl-msft · Accepted solution]

Hello @Welsh_Will ,

I created two test tables with one or many relationships based on shipment fields.

Next, create a measure like this:

Measure = var per_weight=
CALCULATE(DIVIDE(MAX('Table (2)'[Cost]),MAX('Table (2)'[weight])))
return CALCULATE(per_weight*MAX('Table'[Weight]))

.pbix example

Best regards
Liang
If this post helps, then consider Accept it as the solution to help other members find it faster.

[Anonymous]

Thank you!

[DataInsights]

@Anonymous,

How are orders and shipments associated in the data model? Does the Orders table have a column for shipment number?