Choose a value between multiple rows based on a quantity

[GVSmits]

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 :

ItemID	Product	Quantity (low)	Quantity (high)	Price/unit
1	Product 1	1	5	10
1	Product 1	6	10	8
1	Product 1	11	20	7
1	Product 1	21	30	6
2	Product 2	1	2	20
2	Product 2	3	4	18
2	Product 2	5	6	16

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.

[v-rzhou-msft · Accepted solution]

Hi @GVSmits ,

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.

[jgeddes]

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.

[GVSmits]

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.