locked
Trying to add calculated field / KPI RRS feed

  • 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

Answers

  • 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 Michael Amadi Friday, September 5, 2014 5:56 AM
    • Marked as answer by Charlie Liao 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 Michael Amadi Friday, September 5, 2014 5:56 AM
    • Marked as answer by Charlie Liao 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