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 Item Area Delivery Time Value 1 A ...
  • 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]