Multiplication with different granularities

[joshua1990]

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?

[Anonymous · Accepted solution]

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]

@joshua1990 , not sure I got it. You are filtering order #. no filter in avg time. So the number will not match with sum

[Anonymous]

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.

[Greg_Deckler]

@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?