Mapping the Average progress for individual Project based on Milestones for each project

1

Hi all!

Here is what I’m trying to achieve:

  • I have 4 separate projects
  • For each project I have 3 milestones (which are related in a table to their mother project) for which I track progress
  • I would like to have one table that tracks the overall progress for each project based on the progress of each milestone linked to its mother project
  • In the document (link below) this overall progress should be mapped in the column “Overall Progress” of the “Project” table.

I’ve setup a quick document to demonstrate what I’m trying to achieve.
https://coda.io/d/Average_dMR5LMJ_wTH/_suVVS

Thanks in advance for any guidance on this.

Best,
Nat

2

Dear @Nathaniel_Daudrich,

Please can you make the document available (no access request)?

Thank you,
//JP

1 Like
3

Hard to say without seeing your doc, but here’s my guess how you’d want to approach this:

  1. In Project table, make a column where you’ll pull a list of all sub-data (tasks or milestones, wherever you track your progress value). I.e., to select all rows in Milestones table whose Project is this row’s project, the formula would be like Milestones.Lookup(Milestones.Project = thisRow)

  2. When you have that sub-data in your Project table, you can aggregate on those groups however you like, e.g. get .Average() of completion percentage, or divide a .Sum() of completed subtasks by a .Sum() of total subtasks etc — depending on how you track your progress.

Those are the common steps. Of course the specifics depend on how your data is actually organized there, which I can’t see.

4

Hi @Paul_Danyliuk and @Jean_Pierre_Traets ,

Thanks a lot for your reply and my apologies for not giving you proper access to the document! I’ve given ‘edit’ access to those with this link now:

My current formula does not work correct :neutral_face:

Thanks again in advance for any help,
Nat

5

Fixed the doc for you. The formula is simple and as I anticipated:
Milestones.Filter([Related Project] = thisRow).Progress.Average().Round()

(I added rounding to integers so that values don’t jump around)

6

Thanks @Paul_Danyliuk - I implemented it inour real-life project and it worked a charm.

Thanks for all your help in the learning process.

See you again soon :joy:!

Best,
Nat