Forum Discussion
Creating a weight/cost ratio measure
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!
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]))Best regards
Liang
If this post helps, then consider Accept it as the solution to help other members find it faster.
3 Replies
- DataInsights
Super User
Anonymous,
How are orders and shipments associated in the data model? Does the Orders table have a column for shipment number?
V-lianl-msftCommunity Support
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]))Best regards
Liang
If this post helps, then consider Accept it as the solution to help other members find it faster.
