Forum Discussion
Fluctuating Rebate Amount
Hi!
I'm trying to find a way to calculate a rebate amount while being able to adjust the discount field. Depending on the discount amount, there are different tiers of rebates. Any discount under $1.00 will receive a 50% rebate. If it's over $1.01 up to a max of $2.00 then the $1.00 will be at 50% and anything over $1.01 will receive a 70% rebate. So if the discount is $1.75, $1.00 with achieve a $0.50 rebate and $0.75 will achieve a 70% rebate for a total rebate of $1.02.
| Product | Price | Max Discount | Discount | Min | Max | Rebate | Rebate Amt |
| Hamburger | $20.00 | $2.00 | $1.00 | $.01-$1.00 | $1.01-$2.00 | 50% | $0.50 |
| Hamburger | $20.00 | $2.00 | $2.00 | $.01-$1.00 | $1.01-$2.00 | 70% | $1.19 |
| Steak | $20.00 | $2.00 | $1.00 | $.01-$1.00 | $1.01-$2.00 | 50% | $0.50 |
| Steak | $20.00 | $2.00 | $1.50 | $.01-$1.00 | $1.01-$2.00 | 70% | $0.84 |
| Pork | $20.00 | $2.00 | $1.00 | $.01-$1.00 | $1.01-$2.00 | 50% | $0.50 |
| Pork | $20.00 | $2.00 | $1.75 | $.01-$1.00 | $1.01-$2.00 | 70% | $1.02 |
Hi Anonymous
Apologies, please adjust to the following:
Rebate Calc = VAR _1 = SWITCH ( TRUE () , Rebate[Discount] <= 1 , 0.5 , Rebate[Discount] > 1.01 , ( 0.5 * 1.01 ) + ( 0.7 * ( IF ( Rebate[Discount] > 2 , 2 - 1.01 , Rebate[Discount] - 1.01 ) ) ) , 0 ) RETURN _1I forgot to include the "=" in the first conditional statement of "<=" so it was ignoring anything that was $1.
7 Replies
- TheoC
Community Champion
Hi Anonymous
If I understood your conditional parameters, the below will hopefully achieve what you're after:
Rebate Calc = VAR _1 = SWITCH ( TRUE () , Rebate[Discount] < 1 , 0.5 , Rebate[Discount] > 1.01 , ( 0.5 * 1.01 ) + ( 0.7 * ( IF ( Rebate[Discount] > 2 , 2 - 1.01 , Rebate[Discount] - 1.01 ) ) ) , 0 ) RETURN _1The below is a calculated column with the conditional statements. There is a slight discrepancy between mine and your calcs which are because of rounding or inclusion of the cent difference but you can modify to suit your needs accordingly.
Let me know if you have any questions!
Theo 🙂
- TheoC
Hi Anonymous
Apologies, please adjust to the following:
Rebate Calc = VAR _1 = SWITCH ( TRUE () , Rebate[Discount] <= 1 , 0.5 , Rebate[Discount] > 1.01 , ( 0.5 * 1.01 ) + ( 0.7 * ( IF ( Rebate[Discount] > 2 , 2 - 1.01 , Rebate[Discount] - 1.01 ) ) ) , 0 ) RETURN _1I forgot to include the "=" in the first conditional statement of "<=" so it was ignoring anything that was $1.
TheoC
This looks really good! Would there be a way to create a discount amount slider/text field to see how the rebate amount dynamically changes when the discount amount changes?