The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
102 | |
83 | |
63 | |
57 |
User | Count |
---|---|
254 | |
119 | |
115 | |
100 | |
76 |