Custom Measure Help - Assigning Rebate % In Sense

[#1]

Good Afternoon Everyone,

As a distribution company our bottom line is effected by the amount of rebates that we receive from our supplier. These rebates are a direct result of over all sales for the current fiscal year. 

What I would like to build is a measure that would illustrate the impact that our customers have against those rebates. here is the catch, all the rebate values are not equal so I need to build some sort of "IF" statement that would calculate the impact of potential rebate generation based on the specific sales of that vendor in correlation to that customer.

In my measurement I would need to capture sales specific to this fiscal year. The formula that I am using for that is " ({$<FISYR = {$(=year(Today()))}, " and the field that my vendors are in is " SUPPLR" and the field that contains Total sales is "SASALES"

so what I would need to create is something like this... the problem I am having is when the Account has purchases against many Vendors not just a single vendor and I have to calculate the total impact across many vendors at once.

IF ({$<FISYR = {$(=year(Today()))}, SUPPLR = 'BAYWES' THEN SUM (SASALES * .10),

IF ({$<FISYR = {$(=year(Today()))}, SUPPLR = 'ECM' THEN SUM (SASALES * .05),

Thank you for your help...

Barry

[sinanozdemir · Accepted solution]

Hi Barry,

I guess you can try to do something like this:

Sum(

     If(FISYR = Year(Today()),

          If(WildMatch(SUPPLR, 'BAYWES'), SASALES * .10,

               If(WildMatch(SUPPLR, 'ECM', SASALES * .05

                 )

             )

        )

[ogster1974]

This is an area that interests me. We run a lot of rebate programs for vendors so looking at it from the other side.  Their challenge is getting the data in from Distribution in a consistent format to offer rebates through to T2 partners.

I would say you'd need to include eligible products/services in your model as Account A may have create more sales than Account B but Account B may be selling more of the products eligible for rebate.  Unless you only work with vendors that offer flat rate volume rebates on all sales regardless of product.  This link on product would link Vendors to Account and allow you to roll this up.

[#4]

Sinan,

Thanks you for this feedback this formula worked great.

[#5]

Andy,

This is a great point and something we never thought of... Thank you for putting a different perspective on this.

[sinanozdemir]

You are welcome.