Forum Discussion
joshua1990
Post Prodigy
6 years agoMultiplication with different granularities
Hello everyone! I have to calculate the average delivery time multiplied by the Number of open Orders. I have the following two tables: tblItemMaster Item Area Delivery Time Value 1 A ...
- Anonymous6 years ago
Not sure if this is what you wanted... but you can take it and shape it into something you want.
// Let's assume that // 1. You've got an Orders table with // orders in there OrderID, ItemID, OrderStatus // where OrderStatus is the same for all lines // with the same OrderID. // 2. You've got an OrderItems table with // items that belong to an order from the // Orders table (ItemID is unique across all Orders) // The columns in OrderItems are // ItemID, Area, Delivery, TimeValue // An open order is one that has OrderStatus < 70 in // Orders. // Relationship: Orders[ItemID] * <-one-way- 1 OrderItems[ItemID]. // One order can have multiple items but each item in // OrderItems can belong to only one order. // // The above setup is a bit strange. What it really should be // is this. // Orders - stores data on orders and each line is one order. // Items - stores all available items that an order can contain. // Order2Item - stores the relationship between an order and an item. [# Open Orders] = CALCULATE( DISTINCTCOUNT( Order[OrderID] ), KEEPFILTERS( Order[Status] < 70 ) ) [Avg Delivery Time Per Order] = IF( HASONEVALUE( Orders[OrderID] ), AVERAGE( OrderItems[Delivery Time] ) ) [Avg Delivery Time Per Open Order] = IF( [# Open Orders] = 1, AVERAGE( OrderItems[Delivery Time] ) ) [Avg Delivery Time] = AVERAGEX( VALUES( Order[OrderID] ), [Avg Delivery Time Per Order] ) [Avg Delivery Time for Open Orders] = AVERAGEX( VALUES( Order[OrderID] ), [Avg Delivery Time Per Open Order] ) [Final Measure] = [Avg Delivery Time for Open Orders] * [# Open Orders]
amitchandak
Super User
6 years agojoshua1990 , not sure I got it. You are filtering order #. no filter in avg time. So the number will not match with sum