Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 🙂

3 REPLIES 3
v-piga-msft
Resident Rockstar
Resident 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,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.
Anonymous
Not applicable

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

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.