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]
Anonymous
6 years agoNot applicable
Please give some MEANINGFUL example. The amount of data you've shown is not enough. By the way, if there are multiple items on an order and they get delivered at different times, then I think you'll first have to calculate the average delivery time per one order (so averaging over items of the order) and then average these averages (over orders) and then multiply this average by the number of orders.