Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
haypowerbi
Frequent Visitor

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

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
)
6 REPLIES 6
haypowerbi
Frequent Visitor

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

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

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)

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

 

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

lbendlin
Super User
Super User

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors