cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## 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 @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 3
Community Support

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.

Super User

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.

Proud to be a Super User!

Anonymous
Not applicable

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors