cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Calculate Customer Rebate based on Tiered Cumulative SalesFYTD

Hoping someone out there can help me. I want to calculate a customer rebate based on the cumulative sales multiplied by a  % when sales reach certain levels, eg:

FYTD Sales > 400K , FYTD Sales * 0 ,

FYTD Sales > 400K && FYTD Sales < 500K , FYTD Sales * 0.02

(Above only on the sales between 400K && 500K =  100K * 0.02 = 2K)

FYTD Sales >500K, FYTD Sales * 0.045 (Only on the sales above 500k)

I'm trying create the measure: tried if and switch statements, on cumulatve sales and Sales DatesYTD, but can't get it to work.

Problems I'm having are either I calculate the right figures but it won't display properly over months (to show montly calculation ), or the measure shows correctly over months but calculates incorrect figures ( on the whole sales including the first 400).

Anyone any ideas or done something similar, appreciate all the help I can get,.

Thanks

1 ACCEPTED SOLUTION
Community Champion

try this, I've put the input values as variables so it's easier to adjust in the future

```Rebate =
VAR AmountSold = [FYTD Sales]
VAR UpperValue = 500000
VAR UpperRebate = 0.045
VAR LowerValue = 400000
VAR LowerRebate = 0.02
RETURN
IF (
AmountSold > UpperValue,
( AmountSold - UpperValue )
* UpperRebate
+ ( UpperValue - LowerValue )
* LowerRebate,
IF ( AmountSold > LowerValue, ( AmountSold - LowerValue ) * LowerRebate, 0 )
)```

Thank you for the kudos 🙂

3 REPLIES 3
Microsoft Employee

Hi @CMAC_Terry,

Thanks,
Angelia

Community Champion

try this, I've put the input values as variables so it's easier to adjust in the future

```Rebate =
VAR AmountSold = [FYTD Sales]
VAR UpperValue = 500000
VAR UpperRebate = 0.045
VAR LowerValue = 400000
VAR LowerRebate = 0.02
RETURN
IF (
AmountSold > UpperValue,
( AmountSold - UpperValue )
* UpperRebate
+ ( UpperValue - LowerValue )
* LowerRebate,
IF ( AmountSold > LowerValue, ( AmountSold - LowerValue ) * LowerRebate, 0 )
)```

Thank you for the kudos 🙂

Helper I