Forum Discussion
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
v-piga-msftResident Rockstar
Hi Anonymous ,
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,100I 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
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
- v-piga-msft
Hi Anonymous ,
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