Aggregate Subtraction Based on Rank

[Anonymous]

I'm trying to set up a column which incrementally subtracts demand from capacity based on rank (#1 rank will subtract demand from capacity, then #2 rank will subtract its demand from remaining capacity, etc.). I'm pulling all of the data together with an append query, so the capacity does not exist at the project (demand) level but at the group level. My ideal end state would be a matrix visual that shows the project (demand) and rank as rows and the groups (supply) as columns. The values would display the remaining capacity after accounting for demand in each respective row. Does anyone have experience setting up a measure like that?

In the example below, let's assume the following:

Group 1 starts with capacity of 200, Group 2 with capacity of 500 and group 3 with capacity of 1000

Each Project has a demand of 20 

Group capacity values independent of each other

**Its critical for the calculation to happen in rank order, as the demand will vary for each project

Rank	Project Name	Group 1	Group 2	Group 3
N/A	CAPACITY	200	500	1000
0	Project 1	180	480	980
1	Project 3	160	460	960
2	Project 19	140	440	940

[v-chenwuz-msft]

Hi @Anonymous ,

Is this you want?

Please use two table , one contains rank, project name, demand, another one group and capactiy like the following

Then create measure via this code:

Measure = 
var _rank = MAX('Table'[rank])
var _demand = CALCULATE(SUM('Table'[demand]),FILTER(ALLSELECTED('Table'),[rank]<=_rank))
var _capacity = SUM(capacity[CAPACITY])
return
IF(HASONEVALUE('Table'[rank]),_capacity-_demand,_capacity)

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks very much! This is super close to what I'm trying to accomplish. One item I failed to mention is that there can be multiple units of demand per project. These units are tied to the groups.

Below is an example of how demand differs by project & group. How would you update the measure to account for this?

Project Name	group	rank	demand
Project 1	Group1	1	20
Project 3	Group2	2	30
Project 19	Group3	3	0
Project 1	Group1	1	50
Project 3	Group2	2	100
Project 19	Group3	3	300