Subtract Project Demand by Group Based on Ranking

[Anonymous]

Hello,

I'm trying to create a measure that will subtract the demand a certain project has on a group or multiple groups based on the projects ranking. Each of the groups has an X capacity to start, however; the demand for each project differs by # of hours and  which group the demand is for.  I'd like to visualize this with a matrix, where I have rank and project at the row level, and group name and total capacity at the column evel. The values within the matrix will be remaining capacity after subtracting the appropriate demand from each group based on its rank.

For source data, I currently have the demand and  capacity in two seperate tables.

Here is an example:

Rank 1 - Project A has 100 hours of demand from Group1, 0 hours of demand from Group2 and 200 hours of demand from Group3

Rank 2 - Project B has 0 hours of demand from Group1, 0 hours of demand from Group2 and 100 hours of demand from Group3

Rank 3 - Project C has 200 hours of demand froum Group 1, 400 hours of demand from Group2 and 300 hours of demand from Group3

		Group1	Group2	Group3
Rank	Capacity	1000	1200	400
1	Project A	900	1200	200
2	Project B	900	1200	100
3	Project C	700	800	-200

[Anonymous]

Hi @Anonymous,

I'm not so sure how the start capacity is stored, can you please share some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

For your requirement, I'd like to suggest you write a measure formula with a variable to use current group to get the start capacity from the table. Then you can use the current group and rank as conditions to lookup and summary table records and calculate with the start value.

formula =
VAR startVolume = 'start capacity'
VAR currRank =
    MAX ( Table[Rank] )
VAR rollingCapacity =
    CALCULATE (
        SUM ( Table[Capacity] ),
        FILTER ( ALLSELECTED ( Table ), [Rank] <= currRank ),
        VALUES ( Table[Group] )
    )
RETURN
    startVolume - rollingCapacity

Regards,

Xiaoxin Sheng

[Anonymous]

Group	Month	Hours
Group 1	2022-01	160
Group 1	2022-02	160
Group 1	2022-03	160
Group 1	2022-04	160
Group 1	2022-05	160
Group 1	2022-06	160
Group 1	2022-07	160
Group 1	2022-08	160
Group 1	2022-09	160
Group 1	2022-10	160
Group 1	2022-11	160
Group 1	2022-12	160
Group 2	2022-01	160
Group 2	2022-02	160
Group 2	2022-03	160
Group 2	2022-04	160
Group 2	2022-05	160
Group 2	2022-06	160
Group 2	2022-07	160
Group 2	2022-08	160
Group 2	2022-09	160
Group 2	2022-10	160
Group 2	2022-11	160
Group 2	2022-12	160
Group 3	2022-01	160
Group 3	2022-02	160
Group 3	2022-03	160
Group 3	2022-04	160
Group 3	2022-05	160
Group 3	2022-06	160
Group 3	2022-07	160
Group 3	2022-08	160
Group 3	2022-09	160
Group 3	2022-10	160
Group 3	2022-11	160
Group 3	2022-12	160
Group 4	2022-01	480
Group 4	2022-02	480
Group 4	2022-03	480
Group 4	2022-04	480
Group 4	2022-05	480
Group 4	2022-06	480
Group 4	2022-07	480
Group 4	2022-08	480
Group 4	2022-09	480
Group 4	2022-10	480
Group 4	2022-11	480
Group 4	2022-12	480

 Here is the source data for capacity/group/month.