DAX Formula?

[Craigc3814]

I have two table one is my project table and it contains all information regarding any projects that we have. I also have a Timesheet table that gives me all employees timesheet data.

Both tables contain 3 identical column Project Number, Project Task Number, Project Subtask number.

I want to look at the Budget Hours For each Project Number, Project Task Number, and Project SubTask number on the Project table and subtract the total of all hours charged to each one from the Timesheet table


Here is an example of how data would look on the Project Table

Project Number1 Project Task Project Sub Task Budget Hours
1111 1 1 10
1111 1 2 15
1111 2 1 30
1112 1 1 100

Here is how time is charged on the timesheet table

Employee Project Number Project Task Project Sub Task Hours Charged
1 1111 1 2 3
1 1111 1 2 1
2 1111 1 2 4
3 1111 1 1 1
3 1112 1 1 8
1 1112 1 1 8

 

[peter789]

Why do you want to use DAX? Easy to accomplish in Power Query. Group the timesheet table by Project, Task and Sub-Task. Sum the budget hours. Merge the project table with the summarised timesheet linking on Profect, Task and subtask. Expand table to get Charged Hours. Create a new column which is budget-charged hours.
Peter

 

[Craigc3814]

Yea, it was a completely dumb question. I am pretty new to Power BI but would rank myself as high intermediate in PQ (I can figure out anything in PQ so far just probably not always in the most efficient way).

What I ended up doing was creating an additional column in query editor that merged the three columns and made a relationship on that. It worked perfectly. I just did not realize that DAX would automatically sum everything for me from the timekeeping table. I thought issues would arise but nope, none. I did not want to go the grouping method because I did not want to lose the line by line data so that on another sheet people could look at who all had charged time to a project, how much time they charged, and when they charged it.

Thank you for your response!!