average in straight table

[alexpanjhc]

Hi

I need to calculate the average utilization in the attached picture.

I need to get an average rate for the entire group by using the billed hours total/ standard hours total.(they are both marked red in the pic)

and the rate should show in  average utilization.

How do I achieve that?

Thanks!

[#2]

Sum(TOTAL billed_hours)/sum(TOTAL standard_hours)

should help.

--

Regards,

Kiran Rokkam

[jagan]

Hi,

You can get the total value of a dimension by using the TOTAL keyword in the aggregate function.  By using TOTAL it ignores the dimensions in the chart.

=Sum(TOTAL billedHours) will give you the total billed hours.

=sum(TOTAL standardHours) will give you the total standard hours.

Hope this helps you.

Regards,

Jagan.

[alexpanjhc]

Both billed hours and standard hours are calculated fields. billed hours used a set analysis and standarad hour was a variable.

billed:

Sum

({<Module={'FEE'}, bill_status={'B'} ,[Trans Date]= {'>=$(=vStart)<$(=Addmonths( date(vStart), num(Interval)))'}>} Hours)

Standard:

if

(Column(1)=0,0, if ( date(SelectStart)<date(EffectDate) and   (AddMonths(vStart , Interval) -1)<EffectDate , 0,
  if ( date(SelectStart)<date(EffectDate) and   (AddMonths(vStart , Interval) -1)>EndDate , 173.3*  ((EndDate-EffectDate)/30) ,
  if ( date(SelectStart)<date(EffectDate) and   (AddMonths(vStart , Interval) -1)<= EndDate , 173.3*(((AddMonths(vStart , Interval) -1)-EffectDate)/30) ,
  if ( date(SelectStart)>=date(EffectDate) and   (AddMonths(vStart , Interval) -1)<= EndDate   ,  173.3* ((AddMonths(vStart , Interval) -1)-SelectStart )/30 ,
  if ( date(SelectStart)>=date(EffectDate) and   (AddMonths(vStart , Interval) -1)> EndDate   ,  173.3*  (EndDate-SelectStart )/30 ,
  if ( date(SelectStart)>=date(EffectDate) and   date(SelectStart)> EndDate   ,  0
)
)
)
)
))

)

so i tried what you guys suggested

sum(total

sum({<Module={'FEE'}, bill_status={'B'} ,[Trans Date]= {'>=$(=vStart)<$(=Addmonths( date(vStart), num(Interval)))'}>} Hours) )/

sum( total $(vStd1))-> this is the variable so that I do not copy a lot of codes.

this is not working, it showed the dash. I then replaced the variable using the actual set analysis. I was unable to get the correct answer either.

where was I wrong?