Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Subtract Project Demand by Group Based on Ranking

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

 

  Group1Group2Group3
RankCapacity10001200400
1Project A9001200200
2Project B9001200100
3Project C700800-200

 

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago
      GroupMonthHours
      Group 1            2022-01160
      Group 12022-02160
      Group 12022-03160
      Group 12022-04160
      Group 12022-05160
      Group 12022-06160
      Group 12022-07160
      Group 12022-08160
      Group 12022-09160
      Group 12022-10160
      Group 12022-11160
      Group 12022-12160
      Group 22022-01160
      Group 22022-02160
      Group 22022-03160
      Group 22022-04160
      Group 22022-05160
      Group 22022-06160
      Group 22022-07160
      Group 22022-08160
      Group 22022-09160
      Group 22022-10160
      Group 22022-11160
      Group 22022-12160
      Group 3   2022-01160
      Group 32022-02160
      Group 32022-03160
      Group 32022-04160
      Group 32022-05160
      Group 32022-06160
      Group 32022-07160
      Group 32022-08160
      Group 32022-09160
      Group 32022-10160
      Group 32022-11160
      Group 32022-12160
      Group 42022-01480
      Group 42022-02480
      Group 42022-03480
      Group 42022-04480
      Group 42022-05480
      Group 42022-06480
      Group 42022-07480
      Group 42022-08480
      Group 42022-09480
      Group 42022-10480
      Group 42022-11  480
      Group 42022-12 480

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