I would like to write a dax code that can do all of the commision rates sumiltanously or with little repetitive processes.
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
Please assist?
Template_commission.pbix (151.8 KB)
Hi @Yrstruly,
Can you please explain a little how you calculate the commission rates ?
I tried to understand your Excel file, but it’s not very clear to me…
Best regards,
JBocher
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 R50010% + R30015% + 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 R1000100% + R600100% + 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.
Hi @Yrstruly,
To answer to your problem, I’ve created lots of tables and measures.
It will be very long for me to explain every thing I did step by step, because you provide a PBIX file without any measures to start with, so I’ve created all of it from scratch 
My advice is to see what I’ve did in my PBIX file and to come back to me if you have any questions.
Template_commission (1).pbix (156.9 KB)
You can see all my measures in “Key Measures”.
You can also see the model I’ve created here
The data you provided are not complete, for instance some Employees are not in the Salaries table.
I didn’t take in account your VAT problem, because I don’t know the percent I need to use, but I think you can change it easily in my measure 
Here are the different steps of my solution :
1/ Create two tables for the billing cycle and for the salary cover.
2/ Create the measures of Monthly Salary and Sales in Month
3/ Calculate the percent of Salary Cover
4/ Calculate the Commission link to each product and the Salary Cover table
At the end you obtain a table with all the commissions.
I just have one remark.
For next time, it would be easier for us if you could start working on your problem and we could provide help to you if you are stuck.
I prefer helping you, instead of doing the entire work for you.
Best regards,
JBocher
Hi @Yrstruly , did the response provided by @JBocher help you solve your query?
If not, how far did you get and what kind of help you need further?
If yes, kindly mark as solution the answer that solved your query.
Hi @Yrstruly, we’ve noticed that no response has been received from you since September 24.
We just want to check if you still need further help with this post?
In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
Hi @Yrstruly, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please create a new thread.
14% Vat can you edit the dax code?
