7 Replies Latest reply on Feb 13, 2019 5:31 AM by Jim Dehner

# calculate bonus for agent dependend on calculation rule

Hi everyone

today I have an issue with a little bit complicated calculation in tableau.

In this case I have to calculate bonus for employee.

If an agent has reached more than 75% of (Sales/Target) then an agent is qualified for bonus.

But only if the company  results (total of (Sales/Target)) is  60% or more.

If  company total of (Sales/Target) is  60% or more then bonus also depends of the individual margin for each agent, see table below and my attached workbook.

Example:

Frank is qualified for bonus calculation and company total  (Sales/Target) is >=60% and the individual margin of Frank = 9.9%

If we look to the bonus definition into the table then we'll see that we have to multiply 0.05% to Frank's sales -> 0.0005 x 452,612.99 .

Then Frank will get 226.30 as a bonus.

Is this kind of calculation possible?

Thanks for any ideas.

• ###### 1. Re: calculate bonus for agent dependend on calculation rule

Hi,

Please find the below attachment and screen shot,if it is working then marked as Correct or Helpful.

Note:-See Sheet 2

Thanks,

Amar D. Savale

• ###### 2. Re: calculate bonus for agent dependend on calculation rule

Thanks but the condition is missing where the total company margin has to be checked. If it's under 60% in general no one will get a bonus calculation. And in your example agents who are not qualified for bonus also get the bonus calculation.

• ###### 3. Re: calculate bonus for agent dependend on calculation rule

Hi

I saw your post this morning and got a little confused in the terminology - not certain that I have it down but here is what I did

first this is the company check (I think) and also creates level (your rows in the matrix)

then the bonus % becomes

and this is the bonus

it returns this

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 4. Re: calculate bonus for agent dependend on calculation rule

@Jim What can I do if my target sales are on a different data source? Because I can't use LOD if data are on different datasource.

• ###### 5. Re: calculate bonus for agent dependend on calculation rule

Hi Patrick

if that was an issue it should have been mentioned in you first post -

now you have 2 sources - what are the sources - apparently you blended the 2 together - can you join them together rather than blending -

JIm

• ###### 6. Re: calculate bonus for agent dependend on calculation rule

I tried to join it but there were other issues. There is no trick like wondow function in order to check some values?

• ###### 7. Re: calculate bonus for agent dependend on calculation rule

Patrick

I would have to see the data and the structure - window functions are probably not the answer - they happen at the absolute bottom of the order of operations (even after grand totals)

their results can only be used in other window functions -  if you are concerned about confidentiality you can either use dummy data - just need enough to see what is coming from each source and the data level or you can contact me at my business email - marketanalyticsllc@gmail.com

Jim