Forum Discussion

joshua1990's avatar
joshua1990
Icon for Post Prodigy rankPost Prodigy
6 years ago
Solved

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

ItemAreaDelivery TimeValue
1A1050
2B15500

 

tblOrder

OrderItemStatus
AA1150

 

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's avatar
    Anonymous
    6 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's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity 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?

  • Anonymous's avatar
    Anonymous
    Not 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.
  • joshua1990 , not sure I got it. You are filtering order #. no filter in avg time. So the number will not match with sum

  • Anonymous's avatar
    Anonymous
    Not 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]