I have a list with the following dimensions that I am interested in developing a KPI for each user. For example, I have User1, User2,...User(n). Each of these users will have to develop certain training credits every year with certain #s of training hours. Example, User1 may need 10 units of X-train, User2 may need 10 units of X-train and 20 units of Y-train.

I need to develop a KPI that looks at the units and says for each X-train criteria if they are getting it done.

Here's a use-case:

User1 will enter X-train event 1 for 5 credits in a column in SP User 1 will enter Y-train event for 3 credits in a column in SP User 1 will enter X-train event 2 for 2 credits in a column in SP

The columns in SP are: UserName, X-Train or Y-Train, NumCredits and a calculated column for the year (TEXT[TrainingDate], 'yyyy').

I think I need a calculated column that says aggregates X-Train and Y-Train for the year. Then I can just create an individual KPI for User1-XTrain-AnnualKPI and do a %-complete of a static value (15/20 = 80% of goal 100%).

Any thoughts on how to implement the calculated columns for aggregating training credits by training type for the year?

I am doing this all OOB.


So I presume User1 can have multiple rows in the list.

A Calculated Column can only do calculations within one row.

With you OOB request I would say:

  • You can try the SharePoint Designer Workflow route where you find all Rows of User1 and calculate an average. (and then store it somewhere)

  • You can use a Calculated Column with some (consealed) HTML and JavaScript in it to display an average in a view see ViewMaster365.com - Sum a Calculated Column - this example does not work in Grouped views, which you probably want

  • You can Develop CSR/JSLink code to do anything you want in the SP JavaScript Object Model

  • You can alter the XSLT with SharePoint Designer and have the View display an average

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.