May 07 2023 05:09 AM
Hi i need help creating measures on power pivot to calculate capacity of my team, i have extracted only one person's workload so far in the attached sample file, pls let me walk you through:
R value:
R4 = 2, R3 = 1.3, R2 = 1,R1 = 0.1
S value:
S1= 1, S2=0.2
Extra System Count = DISTINCTCOUNT([System Count])-DISTINCTCOUNT([Group Count])
Extra System Point = 0.2
Extra Group Count =IF(DISTINCTCOUNT([GROUP])>=1,DISTINCTCOUNT([GROUP])-1,DISTINCTCOUNT([GROUP]))
Extra Group Point = 1
Formula for my capacity calculation for each distinct ID = R Value * S Value + Extra System Count * Extra System Point + Extra Group Count * Extra Group Point
Will need to total the point for all distinct ID summed up to see the total capacity.
Appreciate your help.