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
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]