power query formula help

[sciartilli]

good morning,

what is the easiest formula to take the first amount of $$$ multiply by certain %, then the next amount and so on...

so for example if I have $15,500 in sales and a commission bracket is:
$0-5K- 5%
5,001- 10,000- 3%
10,001- up - 2%

I want the formula to multiply the first 5k by 5%, then the next 5K by 3%, and next 5000 by 2%

 

[GraH]

Did not test all scenarios, but try

Book1
	A	B
1	Amount	Commission
2	15500	410
Sheet2

Cell Formulas
Range	Formula
B2	B2	=MIN(A2,5000)*5%+MAX(0,MIN(A2-5000,5000))*3%+MAX(0,(A2-15000))*2%