Join Bonnie Biafore for an in-depth discussion in this video Creating custom fields to track velocity, part of Agile Project Management with Microsoft Project.
- You can use a custom field to track the sprint velocities a team achieves. We're going to set up a custom field with the formula to do just that. On the Project tab we're going to click Custom Fields. We want to set up a field to track the completed points. Well that's going to be a number field. In the Type drop-down list, choose Number. Numbers one through three are already spoken for, so I'll just move right on to Number4 and click Rename and I'll call this one Velocity.
Go ahead click OK and now we have the alias in place. To add a formula to this field, down in the Custom attributes section, click the Formula button. That opens up the formula for Velocity dialogue box. Before we setup the formula, lets just take a second to figure out what the formula has to do. If a task is a feature and it's complete the velocity should equal its feature points, because those are the completed points.
If the feature isn't complete, it gets no credit, zero, zip, zilch. And if the task isn't a feature, it definitely gets no points. We have a couple of tests that we have to add. The first thing we're going to do, is we're going to add a function for, if then. Click the Function button, point at General, and the function that you want is, IIF. The Expression is the test for this, if then. Double-click that to select Expression.
Now what do we want for this first test? In another movie, we set up a flag field called Feature, to designate the tasks that are features. The first test is, that the Feature field equals yes. Let's insert that. Click the Field button. That was a Flag field, so point at Flag, then you have to point at Custom Flag and choose our Feature Flag at the top of the list and we want that equal to Yes. So type that in.
That's our first test. Now double-click, truepart. This is what's going to happen if the Feature = Yes. We have to add in a second test that the task is complete. What does that mean? If you guessed that you have to add another IIF function, your absolutely correct. Click the Function button, point at General and choose IIF again. Now you can double-click Expression and we're going to add the second test.
The Feature task has to be complete. Well we're going to use % Complete equals 100 for that test. So click the Field button, point at Number and there's % Complete right at the top of the list. You choose that and then type in = 100. We have our first test Feature = Yes, our second test % Complete = 100. If the task passes both of those, it gets to the truepart. Well what does the truepart do? It should be the Feature Points for the task.
So all we have to do here is insert the Custom Field for Feature Points. Click the Field button and remember that was a Number Field, so point at Number, point at Custom Number, and choose Feature Points. If it isn't complete, it gets nothing. So double-click falsepart and type in 0. In this last falsepart, well if the feature equals no it definitely doesn't get any points, so double-click that and also type 0. That's the formula, go ahead and click OK.
When you see the message about, deleting existing data, we don't have any existing data, so you can click OK without worrying. One last thing to do. Go to the Calculation for task and group summary rows section. We want to Rollup these values to the Summary row, because what we want is that the Sprint Summary row show the velocity for all of the completed features in that sprint.
Once I select the Rollup option, click the down arrow and what do you want, Sum. Each feature task is going to show its completed points, by choosing the Rollup to Sum, the summary task for the sprint will show the completed points for its feature tasks, that is the sprint Velocity. Let's click OK to close the Custom Fields dialogue box. Remember it's always a good idea to do a quick test to make sure that your formula works.
Let's switch to the Summary table. At the top left I'm going to right-click the Select All Cells box and choose Summary. And I'm going to drag the vertical divider in between the table and the time scale, over to the right, that way I'll be able to see more of the table. Now what I'm going to do is, I'm going to insert columns for Feature, Feature Points and Velocity. So I'm going to right-click % Complete up in the heading and choose Insert Column.
I start to type Feature and I see a very short list of my three custom fields that start with that word. I can select Feature from the drop-down list. Now I'm going to right-click % Complete again, insert the second column and this one is for Feature Points. Now the third time when I right-click, I choose insert column and this time I want Velocity. Now you want to scroll down so that we can see the features that are in the product backlog.
We're going to start by changing the Feature cell for the first two features. Just those first two to do a quick test. We're going to change the first one to Yes, and then click the second Feature cell for Validate the address and change that to Yes. Now I'm going to add the feature points. Well let's say that capturing the customer address information is five points, so in that Feature Point cell I'll type 5. And in the Feature Point cell for Validating the address, I'll type 3.
Well nothing has happened yet and that's because I haven't changed % Complete. So let's change the % Complete for that very first Feature to 100. When I press Enter, notice what happened. The formula kicked-in, the task is a Feature and it's complete so Velocity has changed to 5. That's the number of feature points for that Feature. And that number has rolled up to the summary task, that the task belongs to.
So that's also equal to 5. Now let's change the % Complete, for Validation the address to 100. When I do that, of course the velocity for the feature task changes to 3 and sure enough the velocity rolls up so that the summary tasks has a velocity of 8. Now remember in an agile project you're not going to check the velocity in the product backlog. You're actually going to see the velocity for a sprint, so just pretend this summary task, for the purposes of the test, is a sprint summary task, because these two features are complete, the velocity achieved in the sprint is 8.
So the formula is doing what we want. Now that we know that, let's go back and just reset things. I'm going to change % Complete back to 0, for those two features. And I want to hide the three columns that I added to the summary table. I can just drag over those three headings and then right-click in the heading area and choose Hide Column. In another movie you'll see how to show planned velocity and achieved velocity in a report.
LinkedIn Learning (Lynda.com) is a PMI Registered Education Provider. This course qualifies for professional development units (PDUs). To view the activity and PDU details for this course, click here.
The PMI Registered Education Provider logo is a registered mark of the Project Management Institute, Inc.
- Setting project options
- Defining working time
- Setting up custom fields and views
- Creating tasks
- Assigning features to sprints
- Assigning resources to tasks
- Tracking progress
- Generating a burndown report