Calculate rebate amount based on range

[Anonymous]

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.

[v-yuta-msft]

@Anonymous ,

Could you please share some sample data and give the expected result?

Regards,

Jimmy Tao

[amitchandak]

Please try.

1. Use summarize and sum up purchase data by supplier

https://docs.microsoft.com/en-us/dax/summarize-function-dax

2. Then either by using lookup or the formula similar to one given below to populate the new column with rates based on the amount

https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax

Max Rate = MAXX(filter(Bonus ,SummPurchase [Amount] >= 'Bonus'[min Amount] && SummPurchase [Amount] <= 'Bonus'[Max Amount]),Bonus[Percent])

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601