Sum of child records issue

[theonlyhamish]

Having trouble writing the correct expression to sum all child records in and a static column too.

Have tried the sum select function with various versions of adding in the static number but nothing.

Parent table has a 'total' column with this expression SUM(SELECT(childtable[column], [uniqueId] = [_THISROW].[uniqueId])), firstly does this expression need to be in a virtual column?  It doesnt seem to work in the static column.  I then need to add to the sum the fixed number from the parent table [price]

[dbaum]

---

@theonlyhamish wrote:

firstly does this expression need to be in a virtual column?

---

Probably--assuming that you want the sum to update every time that child records are added or revised and not only when the parent record's other columns are revised.

---

@theonlyhamish wrote:

SUM(SELECT(childtable[column], [uniqueId] = [_THISROW].[uniqueId])

---

Try instead leveraging list dereferences:

SUM([Related childtables][column])

---

@theonlyhamish wrote:

I then need to add to the sum the fixed number from the parent table [price]

---

This should be straightforward to include in your [total] column's App formula expression. Do you have a question?

[theonlyhamish]

@dbaum Thank you for your help, i still cannot get my head around how to structure this and where (which column) to insert the expression.

The image reflects my structure and i would like the 'total' column to display the sum of 'original price' which is one record and all related child records from 'additional costs'.

Every time an 'additional cost' from the child table is added the total column should be updated.  I hope that makes sense 😀

[dbaum]

Did you try using the expression as the App formula property in a [Total] virtual column in the parent table?

[theonlyhamish]

---

@dbaum wrote:

Did you try using the expression as the App formula property in a [Total] virtual column in the parent table?

I have successfully summed the 'additional costs' now using a virtual column.  The final step ( i assume) is to have another formula to add this calculated value to the value of the 'original price' column unless i can string together a whole formulae for this?

I have tried an additional column to add a simple sum of the calculated child total and the original price total but get this error;

Column 'Original Price' is used in a SELECT or list dereference expression and should be a List/EnumList of Refs

---

[dbaum · Accepted solution]

If you post the expression that results in an error, then someone likely can advise you how to fix it.

You should be able to use either the SUM function or the + math operator. Be sure to read the linked help articles, paying particular attention to the required syntax and explanations of common problems.

SUM(SUM([Related childtables][column]), LIST([Original Price]))

SUM([Related childtables][column]) + [Original Price]

[theonlyhamish]

Thanks @dbaum i tried the usual math operator + which worked this time, i dont know what i was doing with my syntax earlier as i was getting errors about using the + sign.  All working now though!  Thanks again