# Calculating using a disconnected Table

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!

Hi @korbina. Please upload you work-in-progress PBIX file plus a marked-up screenshot or Excel mock-up of you desired outcome for the forum members to review.
Greg

Please see mock up of the pbix file. Blank area of Rebate % ought to read 6%.

Rebate Calculations.pbix (601.8 KB)

Hi @Korbina. The data in your supporting table has a gap … I’m guessing the last record was to have MIN = 200,000, but it was entered as 2,000,000. Once I changed it to 200,000, the 6% rebate appeared in your visual.
Greg

Greg -

Fantastic, thank you so much.