DAX Formula for Employee Costs Calculation

Overview

The goal is to create a DAX measure that calculates the total employee costs based on client hours (from hrs_data) and the hourly cost of each employee. The measure will join relevant data sources based on the common fields.

Data Tables

1. rev_data: Contains fields:

   • client_id
   • project_id
   • item_id

2. hrs_data: Contains fields:

   • client_id
   • project_id
   • item_id
   • b_hrs (billed hours)

3. salary table: Contains:

   • employee_id
   • employee_hrly (hourly cost, which is a measure)

DAX Measure

EmployeeCosts = 
SUMX(
    SUMMARIZE(
        hrs_data,
        hrs_data[client_id],
        hrs_data[project_id],
        hrs_data[item_id],
        "Total_Billed_Hours", SUM(hrs_data[b_hrs])
    ),
    [Total_Billed_Hours] * RELATED(salary[employee_hrly])
)

Explanation

• SUMX: This function iterates over a table and evaluates an expression for each row, summing the results.

• SUMMARIZE:

  • This creates a summary table that groups the hrs_data by client_id, project_id, and item_id.
  • A new column, Total_Billed_Hours, is created which sums the b_hrs for the current grouping.

• RELATED:

  • This function retrieves the hourly cost from the salary table.
  • It connects to the relevant employee based on the employee_id which should be included in the model relationships.

Practical Illustration

Example Scenario

• Assume we have the following data:

1. rev_data:

   client_id	project_id	item_id
   C1	P1	I1
   C1	P1	I2

2. hrs_data:

   client_id	project_id	item_id	b_hrs
   C1	P1	I1	10
   C1	P1	I2	5

3. salary:

   employee_id	employee_hrly
   E1	20

Calculation Steps

Assuming all employee costs relate to project P1:

• For each item:
  • For item I1:
    • Total Billed Hours = 10
    • Cost = 10 hours * $20 = $200
  • For item I2:
    • Total Billed Hours = 5
    • Cost = 5 hours * $20 = $100

Total Employee Costs

The total will be:

• $200 (I1) + $100 (I2) = $300.

Conclusion

This DAX measure aggregates employee costs based on billed hours and employee hourly rates. It leverages relational functions effectively to derive meaningful insights from multiple data tables, ensuring accurate financial representation in reporting. To further enhance your DAX skills, consider exploring courses on the Enterprise DNA Platform.