Production % Complete - Based on Actual Cost Vs Baseline Cost - Displayed in Visual Report

[Scheduler_SPP]

Hi everyone,

In my previous post below, I was looking for a way to show me how many of the active subtasks under a summary task were complete:

Post Location on site: Home → Project → General Discussion → MS Project (Desktop) - Total % Complete of all Total Number of Active Tasks under a Summary Task

Post Subject Line: MS Project (Desktop) - Total % Complete of all Total Number of Active Tasks under a Summary Task

Post Link: 

https://techcommunity.microsoft.com/t5/project/ms-project-desktop-total-complete-of-all-total-number...

I now am trying to achieve the following goals:

1) Determine per each active subtask, what % Complete by taking the Summary Task Baseline Cost and dividing it by the subtask Actual Cost, and then giving me a %

2) Show the % in the Baseline Cost Report in the respective month the task was actually finished

I have attached some Examples to show what I am looking for and the effects of this in MS Project Desktop and the Baseline Cost Report (Visual Report). The subtasks were baselined to start in Jan 2024 and end in Feb 2024, for the purpose of the example, I am saying we are now in April 2024:

Example 1) Example 1 showing all active subtasks complete, all Actual Costs are the same as the Baseline Cost. The Baseline Cost Report (Visual Report) shows the Baseline Cost + the Actual Cost + What % of production is complete according to the number of active subtasks complete at the time the subtask Actual Finish was inserted

Example 2) Example 2 is showing only 6/10 active subtasks complete and the Actual Cost is the same as the Baseline Cost

Example 3) Like in Example 2, only 6/10 active subtasks have been completed, but the Actual Costs for the completed subtasks vary, thus showing 94% complete at Task 6

Example 4) In this example, two additional tasks have been inserted (Delay 1 & Delay 2), these tasks are not baselined as they are not part of the plan, but they incur cost, thus driving up the Production % Complete and showing 124% after Task 6 has an Actual Finish date

Essentially, the baseline cost is used as a budget for our activities. Each month, the baseline cost value is paid into the bank account for the site, our Financial Director can then view the Baseline Cost Report (With the added field in the Monthly columns) to see where they should have been and what their actual spend is. If the site has gone over the budget, the over expenditure is taken off the next months baseline cost and the site will need to recoup the lost funds themselves.

Please note: I have tried placing the Monthly Calendar in the Rows section of the pivot table and the tasks in the Columns, however, there are too many tasks for Excel to handle this, thus the Baseline Cost Report (Visual Report) needs to shown in the format as in the Examples provided.

I would appreciate any and all advice on how to try achieve this goal. My schedules are in excess of 10 000 line items as they require a lot more detail then what is usually used.

Thanking you all in advance!

Final notes: If anyone can tell me what the specific field name is for a Summary Task (Using for formulas), it would be much appreciated. I understand there could be multiple sub summary tasks, but I have no idea if you can use a summary task/sub summary task in a formula or not?

[#2]

Scheduler_SPP,
Wow, that's a lot of explanation but a quick read says you are trying to do some complex operations. The basic operations are easy enough but become complex in the format you want.

In your final notes you ask about a field name for a summary line. That field name is simply "summary" and it's value is boolean, "yes" or "no". However as I explained in one of my responses to your original post, custom field formulas in Project can only operate on data for the task row. To include data from other rows (e.g. summary) a combination of extra fields must be used, as detailed in Ignacio's process.

In your current request it appears you want to timescale the calculated value for your percent production cost in the Visual Report. Although custom field values can be included in Visual Reports that can only appear as single values, they cannot be timescaled (i.e. custom fields in Project cannot be timescaled).

So, you asked for advice on how to achieve your goals. My advice is to use VBA and I've done stuff like this for years for other users. With VBA you can get exactly what you want. It won't be a pivot table but it can be in the exact format you want in an Excel spreadsheet.

That's my input, others may jump in with their thoughts.
John

[#3]

@Scheduler_SPP 

Unfortunately, in Project it is not possible to reference values from any of the summary tasks of a subtask, within the custom field formulas.

Regards

Ignacio

[#4]

Connect Power BI to your source data and this can be done.

[#5]

Hi John, I always appreciate your quick and detailed responses, thank you very much!
I understand what all I am asking is a lot to ask of MSP (Desktop). I had to ask the question to verify if it was possible or not, but I had the feeling that the functionality within MSP (Desktop) just won’t be able to give me what I am looking for.
My first thought was using VBA to take the pivot table data, arrange it in a database raw data type format on the next sheet, then recreate new pivot tables or a layout on the next available sheet and have the full functionality of Excel to be able to do the calculations I am looking to achieve. My VBA knowledge is zero and I would need to teach myself how to do this, but if I can achieve this, the type of templates I can create, and detailed reporting would add a lot more value to the existing data within the project schedule.
I had searched for BI solutions and like RFromm suggested and came across Power BI which I see would only work on the online/web versions. My first job was financial reporting, using some locally designed software that would interrogate the database and by using certain parameters, dump the data from the database in Sheet 1 of Excel (RAW DATA) and thereafter, multiple sheets could use this data to display informative reports.
Many thanks for your response once again.

[#6]

Scheduler_SPP,
As I said, one VBA macro in Project could do all the calculations, and export the data to an Excel Workbook in whatever format you need.

John