Forum Discussion
Aggregate Subtraction Based on Rank
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 |
2 Replies
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.
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
