Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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])).
Solved! Go to 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.
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.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
95 | |
80 | |
74 | |
66 |
User | Count |
---|---|
130 | |
106 | |
105 | |
86 | |
72 |