cancel
Showing results for
Did you mean:
nordgard 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

 ID step_from step_to SalesBonus MarkedBonus Bonus 8 0 4,999 6 2 1 8 5,000 9,999 7 3 1 8 10,000 999,999,999 8 4 1 15 0 9,999 8 1 1 15 10,000 199,999 9 2 1 15 200,000 999,999,999 10 3 1

Table 2

 ID BonusIncome NetSales 8 10,000 1,000,000 15 100,000 700,000

Any thoughts how this can be done in power bi?

Thank you for your suggestions 3 REPLIES 3 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

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

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 Looking forward to see your solution!

Cheers

Highlighted 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.