Sum Total Row with Project Rows to Find Value of Missing Rows, Add Row w/ Missing Values

[hellyars]

I have two existing rows.   The row where isnull([Project]) is the total.  A record where ! isnull([Project]) is a project.  I want to create a third line, that represents the value of the missing project(s) by summing the total line with any existing project lines (in this example there is only 1).  The new line needs to share the same Year and ID as the total line and needs "All Other Projects" added to Project.

In the existing data, there are dozens of groups like this with 1 total line and 1-N project lines.  Only one group is depicted here.  For a given group, ID will be equal.

WHAT	YEAR	ID	Project	1	2	3	4	5
EXISTING	2021	X4567	[Null]	105	110	125	130	120
EXISTING	2021	X4567	A9X	30	50	40	60	70
CREATE	2021	X5467	Other Projects	75	60	85	70	50

I have a crude solution, bu\t I am curious about what other approaches might be out there.

Thanks

[AngelosPachis]

Hi @hellyars ,

Here's one way I can suggest, I have annotated the workflow for each step so hopefully that clear enough and does the job

Cheers,

Angelos

[kelly_gilbert]

My go-to method would be the same one @AngelosPachis suggested, except I would slightly modify the formula to handle nulls:

This would handle the situation where only the unknown/unlisted projects have a value for a column, but the listed projects do not. For example, say the group total (Value) was 100, and the sum of the "known" projects (Sum_Value) was null. The original formula, [Value] - [Sum_Value], would return null in that situation, but you'd actually want it to be 100.