Forum Discussion
Multiplication 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 | 10 | 50 |
| 2 | B | 15 | 500 |
tblOrder
| Order | Item | Status |
| AA1 | 1 | 50 |
For this purpose I have two measures.
The first one to determine the number of Orders open (Status < 70).
# Order= CALCULATE(
COUNTROWS( tblOrder),
ALL( 'Calendar' ),
tblOrder[Status] < 70
)
And then I have an Avere function to determine the average delivery time:
Delivery Time = AVERAGE('tblItemMaster'[Delivery Time])
With a simple measure like this:
Test = Delivery Time*[# Orders]
> I get a false result.
I need to calculate this on the Area-level, not on Article Level.
For this I choose average, right?
What did I forget?
- 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]
4 Replies
- Greg_Deckler
Community Champion
joshua1990 - Not sure I understand. I assume you have a relationship between your 2 tables, correct? On Item? Also, you are placing Area in your visual, correct? Any chance you can share an example PBIX?
- AnonymousNot applicablePlease 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.
- amitchandak
Super User
joshua1990 , not sure I got it. You are filtering order #. no filter in avg time. So the number will not match with sum
- AnonymousNot 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]