cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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
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

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors