Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Choose a value between multiple rows based on a quantity

Hello,

 

Here is my problem : I have a database "Table Price" with multiple rows for a same product which each row having a different value in the column "Price/unit" because if the customer order more, he pay less by unit. Thus each row also has different value in its columns "Quantity (low)" and "Quantity (high)".

Has showned in the table below :

 

ItemIDProductQuantity (low)Quantity (high)Price/unit
1Product 11510
1Product 16108
1Product 111207
1Product 121306
2Product 21220
2Product 23418
2Product 25616

 

In another table "Order" I have an order with for example : 18 product 1, the value 18 is present in a column "Order.Quantity".  Theses two tables have a relation via "ItemID". 

I would like to create a measure to calculate the final price : for example in our case : 18 * 7

But I can't succeed to select the 7 as value. I was thinking of using LOOKUPVALUE but I would have to say that the "Order.Quantity" > "Quantity (low)" and "Order.Quantity" < "Quantity (high)"; and LOOKUPVALUE doesn't autorize < or >.

 

Total Price =
LOOKUPVALUE('Table Price'[Price/Unit],'Table Price'[ItemID],'Table Order'[ItemID],'Table Price'[Quantity (low)], <='Table Order'[Order.Quantity], 'Table Price'[Quantity (high)], >='Table Order'[Order.Quantity])

 

Thanks in advance for your help and time.

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

     

    I suggest you to create a measure as below.

    Total Price = 
CALCULATE (
    SUM ( 'Table Price'[Price/unit] ),
    FILTER (
        'Table Price',
        'Table Price'[Quantity (low)] <= MAX ( 'Table Order'[Order.Quantity] )
            && 'Table Price'[Quantity (high)] >= MAX ( 'Table Order'[Order.Quantity] )
    )
) * CALCULATE(SUM('Table Order'[Order.Quantity]))

    Result is as below.

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • jgeddes's avatar
    jgeddes
    Super User
    3 years ago

    You can create a calculated column in the order table with...

    orderPriceColumn =
    var _vTable =
    //filter the Price Table to the current itemID and Quantity (low) values less than or equal to current order quantity
    FILTER(
        'Table',
        'Table'[ItemID] = 'Table (2)'[ItemID] && 'Table'[Quantity (low)] <= 'Table (2)'[orderQty]
    )
    Return
    //return the minimum price value of the filtered Price Table
    MINX(
        _vTable,
        [Price/unit]
    )
     
    Hope this helps.
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thank you for your reply.

      It solves my problem to isolate the correct price but I have an error because in the table (2) there is multiple times the ItemID = 1 for example, and Power BI ask for a single value. Would you have an idea to solve this issue please?

       

      The error message : "A single value for column 'ItemId' in table 'ProductionVoucherItems' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

       

       'ProductionVoucherItems' = Table (2) in your formula.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

     

    I suggest you to create a measure as below.

    Total Price = 
CALCULATE (
    SUM ( 'Table Price'[Price/unit] ),
    FILTER (
        'Table Price',
        'Table Price'[Quantity (low)] <= MAX ( 'Table Order'[Order.Quantity] )
            && 'Table Price'[Quantity (high)] >= MAX ( 'Table Order'[Order.Quantity] )
    )
) * CALCULATE(SUM('Table Order'[Order.Quantity]))

    Result is as below.

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.