DAX Formula for Calculating Employee Costs Based on Clients by Project

Requirements Overview

The objective is to calculate the total employee costs associated with clients by project. The relevant tables are:

• salary_cost: Contains employee cost information.
• Rev_data: Contains client hours by project.

Key Points

• Common Field: project_id will be used to join the two tables.
• Formula Context: The formula will calculate total employee costs for each project by summing the employee costs multiplied by the hours worked on that project.

DAX Formula

Total Employee Costs = 
SUMX(
    Rev_data,
    RELATED(salary_cost[Cost_per_Hour]) * Rev_data[Hours]
)

Explanation of the Formula

1. SUMX Function:

   • SUMX is an iterator function that evaluates an expression for each row of a specified table (in this case, Rev_data) and returns the sum of those values.

2. Rev_data:

   • This is the table over which the iterator operates. For each row in Rev_data, we will retrieve client hours associated with the corresponding project.

3. RELATED Function:

   • RELATED(salary_cost[Cost_per_Hour]) retrieves the Cost_per_Hour from the salary_cost table. This function assumes a relationship between Rev_data and salary_cost based on project_id.

4. Multiplication:

   • The formula multiplies the Cost_per_Hour from the salary_cost table by the Hours from the Rev_data table. This gives the total cost for each project as represented by the hours worked.

5. Summing Up:

   • Finally, SUMX aggregates these individual project costs to produce the total employee costs across all projects.

Practical Illustration

Assume the following data:

salary_cost Table

Rev_data Table

Calculation Breakdown

For project_id = 1:

• Total Hours = 30 + 20 = 50
• Total Cost = 50 (Cost_per_Hour) * 50 (Total Hours) = 2500

For project_id = 2:

• Total Hours = 15
• Total Cost = 60 (Cost_per_Hour) * 15 (Total Hours) = 900

Overall Total Employee Costs

• Total = 2500 + 900 = 3400

Conclusion

This DAX formula effectively calculates employee costs based on clients by project by leveraging the relationships between the relevant tables. This approach adheres to good data modeling practices and ensures accurate summation of costs across related entities.

Further Learning

For deeper insights into DAX and advanced calculation techniques, consider exploring courses on the Enterprise DNA Platform.