cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Yrstruly2021
Helper IV
Helper IV

Calculating Commission on Sales

 

My objective is to present a report which shows each agent’s name, the total sales amount for that agent for the month and the commission earned based on the commission policy.

Here is more context for you:

  • Based on the document, the Commission Policy tells you how an agent's commission is calculated. To calculate an agent's commission you need to know what the agent's monthly salary is and how much sales they made for the month for the different services (application hosting, web services, infrastructure hosting). Once you have the agent's monthly salary and total monthly sales generated, you need to calculate the Salary Cover = Total monthly sales/Agent's monthly salary. Say for example, we have an agent who earns a salary of R1000 per month and the agent generates R1000 of sales for the month (of which application hosting = R500, web services = R300 and infrastructure hosting = R200). Salary cover = total monthly sales/agent's salary = R1000/R1000 = 1. This means that according to the commission table provided, the agent falls into the first category because salary cover <= 1. Based on the commission percentages in the table for the first category, the agent's commission for the month would then be R500*10% + R300*15% + R200*7% = R109 Let's say instead of R1000, this agent made R2000 worth of sales for the month (application hosting = R1000, web services = R600 and infrastructure hosting = R400). Then salary cover = total sales/agent's salary = R2000/R1000 = 2. This means that the agent would fall into the fourth category since salary cover >= 2. The agents commission would then be R1000*100% + R600*100% + R400*50% = R1800

  • Commission is calculated on 1 month's service revenue. So if an agent sells an annual product of 500, commission is not calculated on 500, but on 500/12. SSL commission is calculated on 2 months revenue and domains are not included. VAT needs to be subtracted before calculating the monthly service revenue as the data is VAT inclusive.




Please find data set here: https://docs.google.com/spreadsheet...ouid=104129043494164133703&rtpof=true&sd=true
https://docs.google.com/spreadsheet...ouid=104129043494164133703&rtpof=true&sd=true
https://docs.google.com/spreadsheet...ouid=104129043494164133703&rtpof=true&sd=true

And

pbix: https://drive.google.com/file/d/1dGgyQ185t5uJjGZgNPbNyw71jVWvkHLu/view?usp=sharing

 

1 REPLY 1
v-polly-msft
Community Support
Community Support

Hi @Yrstruly2021 ,

How to use the fowllowing three datasets? Why don't you add them into the pbix file? Could you please provide a similpe sample about how to calculating? Providing so many words caused my whole person to be confused.

 

How to Get Your Question Answered Quickly 

 

It is best to provide the output you want in image form.

 

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors