DAX Formula for Calculating Employee Costs

Overview

The objective is to compute the total employee costs associated with client-billed hours by client, project, and item from the rev_data table. Salaries for employees are stored in the salary_cost table, linked by the employee ID. The calculation will sum the product of billed hours and the respective hourly rate for each employee.

DAX Formula

The following DAX formula calculates the total employee costs:

EmployeeCosts = 
SUMX(
    rev_data,
    rev_data[BilledHours] * 
    RELATED(salary_cost[HourlyRate])
)

Explanation of the Formula

1. SUMX Function: This is an iterator function that evaluates an expression for each row in the rev_data table. It performs calculations over a table and then sums the results.

2. rev_data[BilledHours]: This field represents the number of hours billed to the client for each employee, project, and item.

3. RELATED Function: This retrieves the HourlyRate from the salary_cost table, using the established relationship via employee_id. The RELATED function allows you to access columns from related tables within a row context.

4. Multiplication: For each row in the rev_data, the formula multiplies the billed hours by the corresponding hourly rate fetched from the salary_cost table. This calculation occurs for every employee entry in rev_data.

Practical Example

Data Structure

1. rev_data Table:

   • EmployeeID: Unique identifier for employees
   • ProjectID: Unique identifier for projects
   • BilledHours: Total hours worked and billed to the client

   EmployeeID	ProjectID	BilledHours
   1	A	10
   1	B	20
   2	A	15

2. salary_cost Table:

   • EmployeeID: Unique identifier for employees
   • HourlyRate: Hourly wage of each employee

   EmployeeID	HourlyRate
   1	30
   2	40

Computing Employee Costs

• For EmployeeID 1, Project A:

  • BilledHours = 10
  • HourlyRate = 30
  • Cost = 10 * 30 = 300

• For EmployeeID 1, Project B:

  • BilledHours = 20
  • HourlyRate = 30
  • Cost = 20 * 30 = 600

• For EmployeeID 2, Project A:

  • BilledHours = 15
  • HourlyRate = 40
  • Cost = 15 * 40 = 600

Total Employee Costs

The total calculated using the formula will be:

• Total = 300 + 600 + 600 = 1500

Conclusion

This DAX formula effectively computes employee costs based on hours billed, utilizing sumx, direct referencing, and relationships. By following this structured approach, you can implement DAX effectively to solve real-world data challenges associated with employee costs and project billing. For further enhancement of your DAX skills, consider exploring courses on the Enterprise DNA Platform.