Skip to main content
cancel
Showing results for 
Search instead 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

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

 

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.

1 ACCEPTED SOLUTION
v-rzhou-msft
Community Support
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.

vrzhoumsft_0-1686642771280.png

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.

View solution in original post

3 REPLIES 3
v-rzhou-msft
Community Support
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.

vrzhoumsft_0-1686642771280.png

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
Super User
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.



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Anonymous
Not applicable

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.

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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