1 2 Previous Next 17 Replies Latest reply on May 11, 2018 3:51 PM by Shinichiro Murakami

# Calculate Bonus \$ in the Month it is Reached

Hi all. I'm working on a commissions report for my sales team. My actual data source is Salesforce, but I've used Superstore for my sample workbook to post here.

The logic is to add a \$4,000 bonus to the sales rep's commission on the month that \$20,000 in sales is reached for an individual account, and only on that month.

In my Superstore example, I'm pretending

sales rep = category

account = sub-category

So each time a category's sub-category (or, in my world, "a sales rep's account") reaches \$20,000, I want to show \$4,000 as a BONUS in that month. That \$ will then be added to the base commission \$ I have set up in this workbook of 15% of the sales \$.

Also, in the end, I'm not so interested in showing all the sub-category detail. I will need a total commission \$ for each month.

• ###### 1. Re: Calculate Bonus \$ in the Month it is Reached

I think you can do this with 2 calcs:

Account Monthly Sales: {fixed [Account],[Month]:sum([Sales)}

then another that is

If Account Monthly Sales >= 20000 then 4000

end

Hopefully that produces what you're looking for

• ###### 2. Re: Calculate Bonus \$ in the Month it is Reached

Hi Brian. This is giving me weird results. In some cases 0, and in other cases big numbers. However, I may have left out a small detail... I want to evaluate the account's sales YTD. And on the month the \$20,000 threshold is hit, add in the \$4,000.

• ###### 3. Re: Calculate Bonus \$ in the Month it is Reached

Ahh I misunderstood the request, my solution was basically if an account reaches 20k in sales in a month they receive the Bonus.  I don't have a good solution of the top of my head for your scenario now that you clarified

• ###### 4. Re: Calculate Bonus \$ in the Month it is Reached

Hi Roopa, in pseudocode;

IF [monthly sales] >20000 THEN [monthly sales]+4000

ELSE [monthly sales]

END

HTH

Peter

• ###### 5. Re: Calculate Bonus \$ in the Month it is Reached

Sorry, I’m probably not being clear in my description. I don’t want to evaluate monthly sales. I need to evaluate year-to-date sales. And at the moment that \$20,000 is recognized, I want to attribute \$4,000 to that month alone.

• ###### 6. Re: Calculate Bonus \$ in the Month it is Reached

Hi Roopa,

Try this;

if RUNNING_SUM(sum([Sales])) >20000 then RUNNING_SUM(sum([Sales]))+4000

else RUNNING_SUM(sum([Sales]))

END

HTH

Peter

• ###### 7. Re: Calculate Bonus \$ in the Month it is Reached

I've tried something similar, and here's what happens-- suppose \$20,000 is reached in May. The calc you're suggesting would add the \$4,000 to May, but also to June, July, etc., since all the following months are > \$20K. I only want the \$4K to hit in May.

• ###### 8. Re: Calculate Bonus \$ in the Month it is Reached

HI Roopa,

Not exactly sure, but try this.

Thanks,

Shin

2 of 2 people found this helpful
• ###### 9. Re: Calculate Bonus \$ in the Month it is Reached

This is hard....

There HAS to be a better way to do this than how I did it. See attached. I haven't explained much because I'm still looking for a better answer. See attached.

Regards,

Gerardo

1 of 1 people found this helpful
• ###### 10. Re: Calculate Bonus \$ in the Month it is Reached

Thanks Shinichiro. This definitely looks like it's going down the right path. Still trying to implement it and will let you know if it works out. However, I'm a little stuck with the table calc detail screenshot you posted showing a "Nested Calculations" option. I'm not seeing that when I edit the table calc. Is there something I need to do to enable that?

Thanks!

• ###### 11. Re: Calculate Bonus \$ in the Month it is Reached

Thanks Gerardo. I'll check it out. Glad someone else thinks this is hard too!

• ###### 13. Re: Calculate Bonus \$ in the Month it is Reached

I'm trying both of these suggestions, and a combination of the two on my actual data set. So close, but can't quite get there!

Gerardo, can you explain to me what's happening in your "testy" calc

INT([Test])%3

That one is giving me strange results. But I don't even know what it's supposed to be doing.

• ###### 14. Re: Calculate Bonus \$ in the Month it is Reached

In plain English:  Return the integer value of test. Divide that integer by 3 and let me have the remainder.  Here is a very good write up explaining  MODULO:

Making Friends With Modulo %

Here is another workbook.  I noticed that if there was null sales all the formulas would break eg  Technology -> Copiers-> Jan and Feb sales.  This updated workbook should take care of that.

Regards,

Gerardo

1 2 Previous Next