Dymanic allocation of capacity based on priority score

[Anonymous]

Hello,

I am working with project data: Capacity data that is defined at the role level, demand data defined at the project number/role level, and a project list defined at the project number level. 

Query	Dim 1	Dim 2	Dim 3	Dim 4	Dim 5
Demand	Project Number	Role	Hours	-	Date
Capacity	-	Role	Hours	-	Date
Project Name	Project Number	-	-	Project priority	-

I would like to assign capacity to projects so that when I drill down from capacity vs demand @ role level to capacity vs demand @ project number level the measure dynamically allocates capacity to the highest priority projects first. 

Currently, my results look like this: 

1st Quater Estimates	Role 1 (IT)	Role 2 (Legal)
Demand/Hours	100	20
Capacity/Hours	75	10

---

Drill-through

---

Role (IT) & Q1	Project 1 (1st priority)	Project 2 (2nd priority)	Sum
Demand/Hours	90	10	100 - Correct
Capacity/Hours	75	75	150 - Incorrect, should sum to 75

How would I get the result:

Role (IT) & Q1	Project 1 (1st priority)	Project 2 (2nd priority)
Demand/Hours	90	10
Capacity/Hours	75	0

Any ideas would be helpful. 

[v-easonf-msft]

Hi , @Anonymous 

I am confused about this table, it does not look like a table that actually exists. Can you show the  more detailed sample data of each table separately？

Best Regards,
Community Support Team _ Eason

[Anonymous]

Does this help?  Currently, there is no established relationship between project number and capacity. I want to create one based on project priority. That is, all available capacity should automatically allocate to the project with the highest priority and any remaining capacity should be allocated to the second highest priority, an so on.

data model