Help with DAX to calculate Resource allocation %

[Anonymous]

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

[v-eqin-msft]

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

[lbendlin]

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.