Measures in PowerPivot

Copper Contributor

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.

0 Replies