Forum Discussion
Help with DAX to calculate Resource allocation %
Hi Team
I have twotables. I need help in DAX to create a heatmap showing resource allocation %.. How each resource is allocated in each month based on their plan hours and capacity hours.
| SL.N | Project Name | Resource Name | Planned Start | Planned Finish | Actual Start | Actual Finish | Capacity hours | Plan hours | Actual Hours |
| 1 | Project A | Sam | 8-Feb-22 | 28-Feb-22 | 8-Feb-22 | 28-Feb-22 | 160 | 140 | 120 |
| 2 | Project B | Ted | 1-Mar-22 | 31-Mar-22 | 1-Mar-22 | 184 | 160 |
| Capacity Table | |||||||||||||
| SL No | Resource Name | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec |
| 1 | Sam | 160 | 160 | 184 | 160 | 172 | 160 | 184 | 184 | 160 | 160 | 160 | 160 |
| 2 | Ted | 160 | 160 | 184 | 160 | 172 | 160 | 184 | 184 | 160 | 160 | 160 | 160 |
2 Replies
- lbendlin
Super User
There's a lot to unpack here. Your capacity table needs to be unpivoted to make it useful. You may also want to add the year to the timestamp
Next you need to decide how you want to handle projects that complete in less than a month (like project A) or projects that stretch over multiple months. I assume your capacity numbers are based on working days in each month? If so then you will want to add a calendar table that indicates that.
Your sample data will not show a heatmap. only a color blob for Sam in February and Ted in March. For a real heatmap you may want to add some more project sample data.
Hi Anonymous ,
Sorry for that the information you have provided is not making the problem clear to me.I'm confused about how resource be allocated.
Refer to:
How to provide sample data in the Power BI Forum - Microsoft Power BI Community
How to Get Your Question Answered Quickly - Microsoft Power BI Community
Best Regards,
Eyelyn Qin
