# Trying to add calculated field / KPI

• ### Question

• I'm trying to add a calculated field in the lower calculation of this PowerPivot data.  If I was in a normal Pivot Table, my calculated field would be this formula, so that I can compute the utilization sliced by any team, location, or agency in aggregate.

Utilization:= ( [WorkingTime] + [AvailableTime] + [OCOTime] ) / [DurationInDays]

I can't figure out how to do this in Power Pivot, but it's a required prerequisite before I can use it as a KPI measure.  Power Pivot just complains that there is no current row for a column named [WorkingTime].

Shawn Keene

Wednesday, September 3, 2014 9:01 PM

• Hi ,

If you are trying to create a measure in the bottom calculation area , you always should use aggregate functions. Your formula should be something like

`Utilization:= SUM( [WorkingTime] + [AvailableTime] + [OCOTime] ) / SUM([DurationInDays])`

Alternatively , you can add a new column called utilization with your formula by clicking add column. And later build a measure something as below

`Total Utilization:= Sum(Utilization)`

First one would be efficient way since it would calculated at the run time from in memory and it will not need to store the Utilization column into the memory.

Hope i understood your question properly and this helps.

Best Regards Sorna

• Proposed as answer by Friday, September 5, 2014 5:56 AM
• Marked as answer by Saturday, September 13, 2014 6:15 AM
Thursday, September 4, 2014 7:57 AM

### All replies

• Hi ,

If you are trying to create a measure in the bottom calculation area , you always should use aggregate functions. Your formula should be something like

`Utilization:= SUM( [WorkingTime] + [AvailableTime] + [OCOTime] ) / SUM([DurationInDays])`

Alternatively , you can add a new column called utilization with your formula by clicking add column. And later build a measure something as below

`Total Utilization:= Sum(Utilization)`

First one would be efficient way since it would calculated at the run time from in memory and it will not need to store the Utilization column into the memory.

Hope i understood your question properly and this helps.

Best Regards Sorna

• Proposed as answer by Friday, September 5, 2014 5:56 AM
• Marked as answer by Saturday, September 13, 2014 6:15 AM
Thursday, September 4, 2014 7:57 AM
• Thanks a ton I will give it a try.

Shawn Keene

Thursday, September 4, 2014 5:08 PM