I am using Power BI to try and create a report of the demand and availability of different employees based on the month and different projects they are working on.
Currently from our server, we pull data into two different tables. First is the ResourceDemand table setup below
| Project ID | Resource ID | date | Demand hours |
|---|---|---|---|
| A | Person A | 01/01/2021 | 4 |
| B | Person A | 01/01/2021 | 3 |
| B | Person B | 01/02/2021 | 3 |
| B | Person A | 01/02/2021 | 3 |
There could be a Resource who is allocated for multiple projects at the same day and have different hours between those project as well as multiple Resources on the same project at a given date. This table would signify their time spent on their assigned projects on a daily basis.
The other table, ResourceCapacity would hold the capacity of every resource:
| Resource ID | date | Capacity hours |
|---|---|---|
| Person A | 01/01/2021 | 7.5 |
| Person B | 01/02/2021 | 7.5 |
| Person A | 01/02/2021 | 7.5 |
Here there would only be one entry per Resource per day. This would signify their total expected hours to work that day.
I was able to relate the tables using the following statement
Combined_tables =
SUMMARIZE (
ResourceCapacity,
ResourceCapacity[Resource ID],
ResourceCapacity[date],
"Demand hours", SUM ( ResourceDemand[Demand hours] ),
"Capacity", ResourceCapacity[Capacity hours]
)
This gives me a table that has the total demand and capacity separated by dates and Resources. My problem now is that I cannot see how many hours are dedicated to a specific project.
I have been stuck on this for a while and have played around with different types of joins and relationships but cannot seem to get the right combination to work. Please let me know if you have any suggestions.
