cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
nordgard Frequent Visitor
Frequent Visitor

Calculating income using a bonus threshold model

Hi,
I am trying to calculate income based on a stepwise bonus model. The thresholds are given in table 1, and the sales amount are shown in table 2.

 

Bonus, or the company's income, is calculated as the sum of:

SalesBonus (at the correct level)*BonusIncome + MarkedBonus (at the correct level)*BonusIncome + Bonus *NetSales

 

For ID 8 the calculation will be: 10000*0.04+10000*0.08+1000000*0.01=11,200
For ID 15 the calculation will be: 100000*0.02+100000*0.09+700000*0.01=8,100

 

Table 1

IDstep_fromstep_toSalesBonusMarkedBonusBonus
804,999621
85,0009,999731
810,000999,999,999841
1509,999811
1510,000199,999921
15200,000999,999,9991031

 

Table 2

IDBonusIncomeNetSales
810,0001,000,000
15100,000700,000

 

Any thoughts how this can be done in power bi?

 

Thank you for your suggestions Smiley Happy

3 REPLIES 3
Community Support Team
Community Support Team

Re: Calculating income using a bonus threshold model

Hi @nordgard ,


Bonus, or the company's income, is calculated as the sum of:

SalesBonus (at the correct level)*BonusIncome + MarkedBonus (at the correct level)*BonusIncome + Bonus *NetSales

 

For ID 8 the calculation will be: 10000*0.04+10000*0.08+1000000*0.01=11,200
For ID 15 the calculation will be: 100000*0.02+100000*0.09+700000*0.01=8,100


 

I have a little confused about your example. 

 

What's mean of 0.04, 0.08 and 0.01 for the calculation of ID 8? I cannot see the value based on your sample data.

 

Could you explain it in details?

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
nordgard Frequent Visitor
Frequent Visitor

Re: Calculating income using a bonus threshold model

Sorry for not explaining properly @v-piga-msft 


A customers income is based in the amount of bonus it generates from sales. For each customer, f.ex. ID 8, i take the bonus level income from table two (BonusIncome), ex. 10,000, and find the propper step/level in table one. In this example the third line for customer 8. I then use the Sales bones and marked bonus percentage also listed in table one, i.e 8% and 4%, respectively, and times this with the sales amount entiteled for a bonus, i.e 10,000. The general bonus reamins constant at 1%.

Customer ones income then becomes: 10000*0.04+10000*0.08+1000000*0.01=11,200 (where 0.04 is equal to 4 %)

 

Hope this clearified things Smiley Happy

 

Looking forward to see your solution!

 

Cheers

Community Support Team
Community Support Team

Re: Calculating income using a bonus threshold model

Hi @nordgard ,

 

One more question, how do we find the find the propper step/level in table 1 based on table 2.


In this example the third line for customer 8. I then use the Sales bones and marked bonus percentage also listed in table one, i.e 8% and 4%, respectively, and times this with the sales amount entiteled for a bonus, i.e 10,000. The general bonus reamins constant at 1%.

Customer ones income then becomes: 10000*0.04+10000*0.08+1000000*0.01=11,200 (where 0.04 is equal to 4 %)


In your example, I could understand that the third record for id 8 in table 1 is the propper step/level, as the BonusIncome in table 2 for id 8 is 10000 which equals to the step_from for the third record for id 8 in table 1. 

 

However,  the BonusIncome for id 15 in table 2 is 100,000 and the step_from for the second record of id 15 in table 1 is 10,000 which are different. So I have confused about the logic that how to find the propper step/level.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.