Matrix Visualization - Time Availability Calculation Issue

[Anonymous]

I've been asked to create a capacity report to show if an employee has been committed to more projects\tasks than they have available in a month. Management has determined that each employee has approx. 174 hrs capacity per month. 

Project	Resource	WorkCat	Month Cat	Hours	Demand Hours	Capacity Hours
Project A	PM	Project	Jul 2020 Demand	40	40	0
Project A	PM	Project	Jul 2020 Commit	40	0	0
Project A	PM	Project	Jul 2020 Gap	0	0	0
Project A	PM	Project	Jul 2020 Cap	174	0	174
Project A	PM	Admin	Jul 2020 Demand	20	20	0
Project A	PM	Admin	Jul 2020 Commit	20	0	0
Project A	PM	Admin	Jul 2020 Gap	0	0	0
Project A	PM	Admin	Jul 2020 Cap	174	0	174
Project A	PM	Non-Project	Jul 2020 Demand	30	30	0
Project A	PM	Non-Project	Jul 2020 Commit	30	0	0
Project A	PM	Non-Project	Jul 2020 Gap	0	0	0
Project A	PM	Non-Project	Jul 2020 Cap	174	0	174

The end result I'm looking for should look like this:

Resource A	Jul-20
Capacity (Capacity Hours)	174
Project (Demand Hours)	40
Admin (Demand Hours)	20
Non-Project (Demand Hours)	30
Available	84

The problem I'm having is that Power BI is using the sum of Capacity Hours so the value comes to 522 instead of 174. This is also affecting the Available Hours result, where Available Hours = Calculate(SumX([Capacity Hours]-[Demand Hours])).

[dax]

Hi @Anonymous , 

You could try below M code 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLlFwVNJRCvAFERABIMurNEfByMDIQMElNTcxLwUoYmIAIwyUYnWI0+ycn5ubWYLQR4pe98QCuB6S7ATrMzQ3geoEsbDqdUzJzczD6lUjAxiBw1oMrXCPGhFwMIZOoryJaR+xnvTLz9PFF6vGBjACh9U4DIB72JiA03HoJ8rbuOzG5vlYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Project = _t, Resource = _t, WorkCat = _t, #"Month Cat" = _t, Hours = _t, #"Demand Hours" = _t, #"Capacity Hours" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Resource", type text}, {"WorkCat", type text}, {"Month Cat", type text}, {"Hours", Int64.Type}, {"Demand Hours", Int64.Type}, {"Capacity Hours", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Project", "Month Cat"}, {{"avg", each List.Average([Hours]), type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Month Cat] = "Jul 2020 Cap")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"avg", "Demand Hours"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "WorkCat", each "Cap"),
    #"Appended Query" = Table.Combine({#"Added Custom", #"Changed Type"})
in
    #"Appended Query"

 

Best Regards,
Zoe Zhi

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

[Anonymous]

Hello Zoe, 

Thanks for your help on this problem, however I am not familiar with the programming language you are using and will have trouble explaining it to others. Is there a way to get the same thing accomplished using DAX functions?

Neel D

[dax · Accepted solution]

Hi @Anonymous ， 

You could create a  calculated column(because you have a "Cap" row which need to show in table), then use measure to achieve this goal. You could refer to my sample for details.

Best Regards,
Zoe Zhi

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