Calculate rebate amount based on range

Hi all,


I have a purchase table and a bonus (rebates) table. Each purchase is a record in the purchase table. I want to create a visual to show the amount of rebate we will get at the end of the year.

Example:
Purchase table:
Purchase 1: supplier A - 20.000€
Purchase 2: supplier B - 5.000€
Purchase 3: supplier A - 30.000€

Bonus table:
Supplier A: 1€ until 20.000€: 2%
Supplier A: 20.001€ until 60.000€: 5%
Supplier A: 60.0001€ until 100.000€: 7%
Supplier B: 0€ until 20.000€: 3%
Supplier B: 20.001€ until 30.000€: 3,75%

Expected result:
Supplier A: 2.500€ (50.000€ x 5%)
Supplier B: 150€ (5.000€x 3%)

Nice to have: visual that shows how much € is left to reach the next level.

Thanks in advance.