## Prompt

## Answer

# 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

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

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

- This is the table over which the iterator operates. For each row in
**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`

.

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

- The formula multiplies the
**Summing Up**:- Finally,
`SUMX`

aggregates these individual project costs to produce the total employee costs across all projects.

- Finally,

## Practical Illustration

Assume the following data:

### salary_cost Table

project_id | Cost_per_Hour |
---|---|

1 | 50 |

2 | 60 |

### Rev_data Table

project_id | Hours |
---|---|

1 | 30 |

1 | 20 |

2 | 15 |

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

## Description

This guide explains a DAX formula to calculate total employee costs for projects by leveraging client hours and salary data, illustrating practical implementation with examples and key points.