cancel
Showing results for
Did you mean: Regular Visitor

## 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 :

 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])

1 ACCEPTED SOLUTION  Community Support

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.

3 REPLIES 3  Community Support

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.  Solution Sage

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. Regular Visitor

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.  