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.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 78 members 1,365 guests
Please welcome our newest community members: