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

Question

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

YEARIDProject12345EXISTING2021X4567[Null]105110125130120EXISTING2021X4567A9X3050406070CREATE2021X5467Other Projects7560857050

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

Thanks

kelly_gilbert · Answer

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.

AngelosPachis · Answer

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

example.yxmd