How to Calculate Price based on Amount of Units Sold

[ACETH]

Hi, hope someone could help

Is there a way to calulate the total price sold of a product within a month, where the price changes based on the amount of units sold?

Example 

Product sold 3000 unit, therefore units 0-1999 = £0.00, 2000-3000 = £0.05*1000 etc

StartUnitsSold	EndUnitsSold	Price
0	1999	£0.00
2000	4999	£0.05
5000	9999	£0.04
10000	19999	£0.04
20000	49999	£0.03
50000	99999	£0.03
100000	249999	£0.02
250000	499999	£0.02
500000	999999	£0.02
1000000	99999999	£0.02

[Anonymous]

Hi @ACETH

Can you provide detailed sample data and the results you expect? So that I can help you better. Please remove any sensitive data in advance.

Regards,

Nono Chen

If this post helps,then please considerAccept it as the solution to help the other members find it more quickly.

[ACETH]

Thank you for your reply, basically the more units ordered the lower the price, but im struggling on how to split the calculation up.

Price Table

PriceID	UnitSoldStart	UnitSoldEnd	Price
1	0	1999	£0.00
2	2000	4999	£0.10
3	5000	9999	£0.08
4	10000	19999	£0.07
5	20000	49999	£0.06
6	50000	99999	£0.06
7	100000	249999	£0.04
8	250000	499999	£0.03
9	500000	999999	£0.02
10	>1000000		£0.01

Orders

OrderID	UnitsSold
1	3000
2	3262
3	4321
4	5433
5	10776
6	6777
7	8888
8	30000
8	5454
10	98765

Expected Price Example

OrderID	TotalPrice	Working Out
1	£100.00	(1-1999 = 0) (2000-3000 = 1000*£0.10)
2	£126.20	(1-1999 = 0) (2000-3262 = 1262*£0.10)
3	£232.10	(1-1999 = 0) (2000-4321 = 2321*£0.10)
4
5	£754.14	(1-1999 = 0) (2000-4999 = 2999*£0.10) (5000-9999 = 4999*£0.06) (10000-10776 = 776*£0.07)
6
7
8
8
10