Hello All -
I have a disconnected rebate parameter table connected to my model that enables me to calculate sizes of an order a customer places and assign the appropriate rebate % and eventually arrive at amount of Rebate dollars we owe the customer.
| Level | Min | Max | Rebate |
|---|---|---|---|
| Level 1 | $10,000.00 | $19,999.99 | 3.5% |
| Level 2 | $20,000.00 | $49,999.99 | 4.0% |
| Level 3 | $50,000.00 | $99,999.99 | 4.5% |
| Level 4 | $100,000.00 | $199,999.99 | 5.0% |
| Level 5 | $200,000.00 | $10,000,000.00 | 6.0% |
Here is the formula I used:
Rebate %Rates PP = CALCULATE(SELECTEDVALUE( ‘OrderSize’[Rebate], BLANK() ),
FILTER(ALL( ‘OrderSize’ ),
[CurrDSP OrderSize PP] >= ‘OrderSize’[Min] &&
[CurrDSP OrderSize PP] < ‘OrderSize’[Max] ))
The formula works perfectly for the levels until the reaches Level 5; where the order is more or equal to $200K. The results for all orders above $200K are showing blank. Is it something I’m missing from the formula above?
Thanks!