How to do Overhead calculations with data from 2 unrelated tables and DAX

[georgekaipanat]

Hi All,

Im new to PowerBi and would like some help with the below issue im facing. 
I have 2 tables. 

The first table has:
Employee Name, Month, Project, Projections for the month and Actual Hours worked. 
E.g. 

ABC, Sept 2023, DID, 160, 150
ABC, Oct 2023, DID, 100, 70
ABC, Oct 2023, BILL, 100, 80
ABC, Nov 2023, DID, 70,
ABC, Nov 2023, BILL, 70,

XYZ, Sept 2023, DID,160, 160
XYZ, Oct 2023, DID,160, 180
XYZ, Nov 2023, BILL, 170,

The second table has: (P.s: There is not relationship defined between the tables)
Month, Total Max Hours for the month
Sept 2023, 160
Oct 2023, 168
Nov 2023, 160

As you can see, an employee can be working on multiple projects each month and also be projected to do higher hours due to different PMs for each project. 

I need to figure out how to do an overhead calculation for each employee and see where there is a mismatch.

Overhead is defined as (MAX Hours for each month - Projected Hours for each month for each employee) 

SO in our example, for ABC, overhead would be:
Sept 2023    0
Oct 2023     -40
Nov 2023    20

And for ZYZ,
Sept 2023    0
Oct 2023     8
Nov 2023    -10

Negative values for Overhead indicates that the employee is Over Projected, Zero means the projection is correct, and Positive value means the employee is Under Projected.

How do I achieve this? 

[v-zhangti · Accepted solution]

Hi, @georgekaipanat 

You can try the following methods.
Measure:

Overhead = 
Var _N1=CALCULATE(SUM(Table2[Total Max Hours for the month]),FILTER(ALL(Table2),[Month]=SELECTEDVALUE(Table1[Month])))
Var _N2=CALCULATE(SUM(Table1[Projections for the month]),ALLEXCEPT(Table1,Table1[Employee Name],Table1[Month]))
Return
_N1-_N2

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[LarryFisherman]

Hi,

Is this the result you are looking for?

This can most likely be solved in both Power Query and DAX, but I solved this using Power Query, where I use group by Employee Name and Month, and aggregates Hours and Projections. Then I join the max hours table, on date, with the project hours table and calculating Overhead.

Query 1 (Project Table):

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRMjDUBSIjAyNjIMfF0wVIGpoZgEhTA6VYHWRlRmjKDEDKzHGqcvL08YErs0BXZoxqmDlIES4lUJMQaiIio/A63cwATRm60yHKLNCVodtoCLUyFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t, Project = _t, Projection = _t, #"Actual Hours" = _t]),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"ID", "Employee Name"}, {"Date", "Month"}, {"Projection", "Projections"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Employee Name", type text}, {"Month", type date}, {"Project", type text}, {"Projections", Int64.Type}, {"Actual Hours", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Employee Name", "Month"}, {{"Monthly Hours", each List.Sum([Actual Hours]), type nullable number}, {"Projections", each List.Sum([Projections]), type nullable number}}),
    #"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"Month"}, #"Table (2)", {"Month"}, "Table (2)", JoinKind.LeftOuter),
    #"Expanded Table (2)" = Table.ExpandTableColumn(#"Merged Queries", "Table (2)", {"Total Max Hours"}, {"Total Max Hours"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table (2)", "Overhead", each [Total Max Hours]-[Projections]),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Employee Name", Order.Ascending}, {"Month", Order.Ascending}}),
    #"Added Conditional Column" = Table.AddColumn(#"Sorted Rows", "Overhead Category", each if [Overhead] = 0 then "Correct" else if [Overhead] > 0 then "Under Projected" else if [Overhead] < 0 then "Over Projected" else "N/A")
in
    #"Added Conditional Column"

Replace source with your own source and change table (2)'s name to your max hour table name.

Let me know if this works.

[georgekaipanat]

@LarryFisherman Thank you for this solution, but as I mentioned, I'm a total newbie in PowerBi and this is totally outside my comfort zone. 

Can I have a simple DAX solution? Also, I have to plot it in a bar chart. so I just need the numbers.