Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have been using Power Pivot, Power Query and Power BI now for about 2 months. Great tools. Also read through several books on DAX and M-code etc, plus watched several training courses. Getting better and better - but can't find a solution to this one. My brain is "stuck", please help me with this one, so that I can get forward...
I have tens of thousands of rows of salary data, which I need to analyze and make calculations for future. Simple sample file here:
The need I have is to make salary calculations so that I can see the sums what each employee is paid. Their total pay is this:
- basic pay, TotalDutyHours x HourlySalary, taking into consideration their own SalaryBase (note that salarybase and salary might raise next year, see tables)
- overtime pay, 1,5 x hourly pay
- Sunday pay, 2,0 x hourly pay
After these are calculated, I want to make a comparison so that I can use the FutureHourlyPay amounts to calculate total salaries for future. So I want to see both of these amounts (current and future) per employee, per work group and work position.
I just can make the calculations work correctly with DAX... So can anyone guide me to correct path... 😉
Solved! Go to Solution.
Hi @Anonymous ,
You can try to use below calculated column formula and measure if it suitable for your requirement:
Current Amounts
Total_salary = 'Work'[TotalDutyHours]*RELATED(Salary[HourlySalary])+'Work'[OverTimeHours]*RELATED(Salary[HourlySalary])*1.5+'Work'[SundayHours]*RELATED(Salary[HourlySalary])*2
Future Amounts
F_Salary = 'Work'[TotalDutyHours]*RELATED(Salary[FutureHourlyPay])+'Work'[OverTimeHours]*RELATED(Salary[FutureHourlyPay])*1.5+'Work'[SundayHours]*RELATED(Salary[FutureHourlyPay])*2
Position Amounts
position_salary = CALCULATE(SUM('Work'[Total_salary]),ALLEXCEPT('Work','Work'[WorkPosition]))
Position Future Amounts
F_position_salary = CALCULATE(SUM('Work'[F_Salary]),ALLEXCEPT('Work','Work'[WorkPosition]))
Group Amounts
group_salary = CALCULATE(SUM('Work'[Total_salary]),ALLEXCEPT('Work','Work'[WorkGroup]))
Group Future Amounts
F_group_salary = CALCULATE(SUM('Work'[F_Salary]),ALLEXCEPT('Work','Work'[WorkGroup]))
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
You can try to use below calculated column formula and measure if it suitable for your requirement:
Current Amounts
Total_salary = 'Work'[TotalDutyHours]*RELATED(Salary[HourlySalary])+'Work'[OverTimeHours]*RELATED(Salary[HourlySalary])*1.5+'Work'[SundayHours]*RELATED(Salary[HourlySalary])*2
Future Amounts
F_Salary = 'Work'[TotalDutyHours]*RELATED(Salary[FutureHourlyPay])+'Work'[OverTimeHours]*RELATED(Salary[FutureHourlyPay])*1.5+'Work'[SundayHours]*RELATED(Salary[FutureHourlyPay])*2
Position Amounts
position_salary = CALCULATE(SUM('Work'[Total_salary]),ALLEXCEPT('Work','Work'[WorkPosition]))
Position Future Amounts
F_position_salary = CALCULATE(SUM('Work'[F_Salary]),ALLEXCEPT('Work','Work'[WorkPosition]))
Group Amounts
group_salary = CALCULATE(SUM('Work'[Total_salary]),ALLEXCEPT('Work','Work'[WorkGroup]))
Group Future Amounts
F_group_salary = CALCULATE(SUM('Work'[F_Salary]),ALLEXCEPT('Work','Work'[WorkGroup]))
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |