Sum By Date(Year) & Customer - If volume exceeds x * y

haypowerbi · ‎02-15-2023

Hey there,

Working on a forumla that will help gain clarity to our reporting for customer rebates. the formula we are working through will provide rebate % by spend for full calendar year by customer.

The data is by day and quanity

Essentially looking for sum by calendar year by customer - if calendar year volume exceeds x then times y - but the system is tiered so if exceeds another x then by y. Essesntially the tiers are 4 steps and different per customer.

WORKING Rebates =

IF(CALCULATE(

SUMX('Sales Data','Sales Data'[Gross Sales]),

FILTER(Customer,Customer[cmp_parent name]="customer-name"))<800000,(

CALCULATE(

SUMX('Sales Data','Sales Data'[Gross Sales]),

FILTER(Customer,Customer[cmp_parent name]="customer-name"))*.02),

0

)

haypowerbi · ‎02-22-2023

The data is by day of order and the rebate is based on total gross dollars of the year and varies for nearly all customers around 100.

Each customer has at least 3 tiers based on gross sales

Date INV Customer Name Gross Sales

12.1.22 A 30

12.24.22 B 15

12.25.22 A 25

1.1.23 C 15

1.2.23 C 13

1.4.23 B 24

lbendlin · ‎02-22-2023

What is the expected outcome based on the sample data you provided?

haypowerbi · ‎02-22-2023

2022 A 55 x rebate (Tier percentage based on sum volume)

2022 B 15 x rebate (Tier percentage based on sum volume)

2023 C 28 x rebate (Tier percentage based on sum volume)

2023 B 24 x rebate (Tier percentage based on sum volume)

lbendlin · ‎02-22-2023

Add a calendar table to your data model. Create a table visual with the calendar year, the customer name, and the Gross sales.

jst_jdennis · ‎02-20-2023

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