Expression based off three variables

[Rurrea]

Ok. Based off sold price of an item in order details…

we are doing an extended warranty that’s has a set price sheet.

conditions to be meet to determine cost of extended warranty

sold price must be greater than [Column b]
sold price must be smaller than [column c]
and user must manually select term (2 year or 4 year) to match Column a

the cost of extended warranty will be the value of column D being that the row match all three variables above

 

[Rurrea]

In order details after adding an item ::: there’s a reference table for extended warranty.

term will be manually selected

but expression needs to lookup with row the total price of item falls between. To determine price of coverage in last column

[WillowMobileSys]

Based on your two posts, I recommend have 2 columns - one for the reference to the Warranty row and a second to capture the cost for THIS sale.  Cost might change over time so you want the customers warranty cost to be unchanged.

For the Warranty reference column, you can use a LOOKUP() or a SELECT() function.  I always use a SELECT() and that expression would be:

SELECT(Warranty[ID], AND([Column B] <= [Sold Price,
                         [Sold Price] <= [Column C], 
                         [Column A] = [_THISROW].[User Selected Term]))

NOTE:  For a REF column to work properly the [ID] must be a row key .

Then for the Warranty Cost column you can use this simple expression [Warranty].[Column D]

I hope this helps!

[Rurrea]

[Order Details] - sold price of actual item (column : Total)

Each line in Order Details : will have an option to add extended warranty by using a column (Extender Warranty) with a Enum : Yes or No

If user selects yes, a reference table will show for [AO Extended Warranty Log]: Columns here will be as show below

OrderDetail Id	Order Id	Product Id	UserEmail	Timestamp	Term Plan	Cost	Policy#	Status	Notes
					Manual Select Option	= Based off three values. The term selected to match in Extended Coverage price list. Also the selling price from order details must be greater (Column C) and less than (Column D) to match value in (Column E).

[Rurrea]

Ive tried using an expression like this for Cost

SELECT(Extended Coverage Price List[Price],
AND(Order Details[Total] >Extended Coverage Price List[Greater than [Tier]],
Order Details[Total] <Extended Coverage Price List[Less than [Tier]],
E. Warranty Log[Term Plan]=Order Details[Extended Warranty]
))

But does not work