Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Salary calculation and analyze for today and future

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:

Sample work and salary table 

 

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... 😉

1 ACCEPTED SOLUTION
V-lianl-msft
Community Support
Community Support

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.

View solution in original post

1 REPLY 1
V-lianl-msft
Community Support
Community Support

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.