- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Community Information
- Community Accounts & Registration
- Using the Community
- Community Feedback
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Calculating income using a bonus threshold model

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

nordgard

Frequent Visitor

Calculating income using a bonus threshold model

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-12-2019
05:47 AM

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

v-piga-msft

Community Support Team

Re: Calculating income using a bonus threshold model

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-13-2019
12:42 AM

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.

If this post

nordgard

Frequent Visitor

Re: Calculating income using a bonus threshold model

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-13-2019
12:57 AM

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

Community Support Team

Re: Calculating income using a bonus threshold model

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-14-2019
01:06 AM

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.

If this post