Suggested price from last sales and volume-based price

[sengpeseth]

Hello, Please kindly help me. I use app sheet to make sales invoice. I have default price for each items, but I may sell different price (cheaper than default price) to some special customers, and some discount (cheaper price) for large quantity of sales.

Each time, I add new item, my price is editable. I want to show 1) default price (normal price that is already set), 2) history price for special customers (the price is this items sold to this customers last time), and 3) when the sales qty reach certain number, I want app sheet to show the suggested wholesale price (which is pre-set based on volume).

Please kindly help me with the expression to get 2 (history price) and 3 price (volume-based price).

Thank you

[Steve]

sengpeseth:

2. history price for special customers (the price is this items sold to this customers last time),

FAQ: FILTER(), LOOKUP(), MAXROW(), MINROW(), REF_ROWS(), and SELECT() Tips & Tricks 💡

How do I get a column value from the last row of this thing only? LOOKUP( MAX( SELECT( My Table[_ROWNUMBER], ([_THISROW].[Thing] = [Thing]) ) ), "My Table", "_ROWNUMBER", "Wanted Column" ) Replace My Table with the name of the table from which you want the column value; Thing with the name of the column containing a value that identifies the thing you want (e.g., Order ID); and Wanted Column with the name of the column whose value you want. See also: MAX()

sengpeseth:

3. when the sales qty reach certain number, I want app sheet to show the suggested wholesale price (which is pre-set based on volume).

help.appsheet.com

LOOKUP()

Get column value from row in table or slice

[sengpeseth]

Thank you so much Steve🙏 I will try as you suggested. Thank.

[sengpeseth]

Hello Steve! thank a lot for your last suggestion. But I need two conditions to be true to return the value of the wanted last sold price.

([_THISROW].[Customer] = [Customer])
([_THISROW].[Item Name] = [Item Name])

Both of which same table: Sales Item[_ROWNUMBER]

I am not familiar with complicated formula. Can you help me how to add two conditions in the Lookup(Max(Select() formula above that you suggested.

Thanks you in advance for your help.