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
How to compute the yearly salary spendings/costs in DAX?
Hi there, As a part of a Power BI HR Dashboard, I want to showcase the yearly salary costs in a card visual as a rolling total per year. How I can transfer this to DAX code? 1. Result to achieve: Example: Test data for year 2018: Card content Cost of Salary per certain year 32.000 € Option to change specific year by slicer on Date Table Columns to work with in fact table EmpID Salary DateofHire TermDate SalaryMonthly 2. Things to consider: Employee, who was hired before a certain year and is active till end of certain year. Salary spendings needs to be calculated over all month of year (EmpID: 1). Employee, who was hired before certain year and is terminated inside certain year. Salary needs to be calculated just for active months (EmpID: 4). Employee, who was hired in certain year and is active till end of certain year (EmpID: 3). Employee, who was hired in certain year and is terminated inside certain year (EmpID: 2). 3. Data Model 3.1 Relationships Date Table & Fact table is connected via inactive relationships ‘Date’[Date] 1->* ‘Fact’[DateofHire] ‘Date’[Date] 1->* ‘Fact’[TermDate] 4. Data Set https://1drv.ms/u/s!ApbrVj8RZyxnkWev_fGDrZQdMhls?e=K3hPy5 I would appreciate, if someone can help me out. Cheers, Martin
PowerBI - salary historic
Hello everyone I have a problem that I am not able to solve, I need to show the employee's salary in a pipeline, but the salary adjustments only occur in some periods of the year. but it is necessary to show me the most recent to date of the filter. In the month less than April 04/19 the value must be 2,674.08 until 07/01/17, then 2,633.04 until 08/17 etc. https://drive.google.com/drive/folders/1LbKVnWinpoOjT8Fi6KTSqOPdyi62Iywk?usp=sharing
