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

Calculations with M?

Is it possible to write this in M?

 

 

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

Just one thing to note. 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. 

See Commision: https://docs.google.com/spreadsheets/d/1AbWQCIPm-LzyEdSiEuJvYMc6tx5sdmmA/edit?usp=sharing&ouid=11021... 

See Pbix: https://drive.google.com/file/d/1KN2fmGx9hsuJaaX0lN5p0NZl_wkhliwE/view?usp=sharing 

 

1 REPLY 1
lbendlin
Super User
Super User

Pretty sure this would be possible to write in M.  But please remember that Power BI is a reporting tool, not a business management tool.  Just imagine what happens if your agents challenge your calculations.  You have no audit trail whatsoever in Power BI.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors