It sounds like you are trying to calculate rebates for customers based on their spending for a full calendar year. You have different rebate tiers based on the customer's spending, and you want to apply the appropriate rebate percentage based on their tier.
Your current formula calculates the rebate for customers whose total sales are less than 800,000, using a fixed rebate percentage of 2%. For customers whose total sales are 800,000 or more, the rebate is set to 0.
To modify this formula to account for multiple rebate tiers, you can use a combination of IF and SWITCH functions. Here's an example formula that you can modify to fit your specific rebate tiers:
Rebate % =
VAR TotalSales = CALCULATE(SUM('Sales Data'[Gross Sales]), FILTER(Customer, Customer[cmp_parent name] = "customer-name"))
RETURN
IF(
TotalSales < 100000, 0,
SWITCH(
TRUE(),
TotalSales < 500000, 0.01,
TotalSales < 1000000, 0.02,
TotalSales < 2000000, 0.03,
0.04
)
) * TotalSales
In this formula, the rebate percentage is calculated using a SWITCH function, which checks the customer's total sales against different thresholds and returns the appropriate rebate percentage. The formula also multiplies the rebate percentage by the customer's total sales to get the actual rebate amount. You can modify the threshold values and rebate percentages to fit your specific rebate tiers.
Note that this formula assumes that you have a "Customer" table that is related to the "Sales Data" table by a customer ID or name. You will need to replace "customer-name" with the actual name of the customer you are calculating the rebate for.
Credit: ChapGPT | https://chat.openai.com/chat
