Get sum of other items value in same table

[Aurelien]

Hi there

I’m working on a schedule tracker, and I need to calculate which duration can I set on a new entry. Basically, I’m allowed to work 8:12 per day.

I used parent/child tables, and in the child table “Detail_Day” I’m using this formula:

List_OtherChilds=
SELECT(
     Detail_Day[key_detail_day],
     AND(
          [Related_Key_Detail]=[_THISROW].[Related_Key_Detail],
          [key_detail_day]<>[_THISROW].[key_detail_day]
     )
)

CALCULATION_otherDetails_Duration_VC=
SUM(
[List_OtherChilds][TotalTime_Detail_VC]
)

Where [TotalTime_Detail_VC] is a sum of various durations
And in initial value, I use the formula:

workTime=
   "08:12:00"-[CALCULATION_otherDetails_Duration_VC]

Each formula, individually, seems to work properly.

When I sync the app, I get this error:

Expression appears to be part of a cycle[…]

I tried something else, based on excellent article from @Steve:

FAQ: FILTER(), LOOKUP(), MAXROW(), MINROW(), REF_ROWS(), and SELECT() Tips & Tricks 💡

How do I do SUMIF() or SUMIFS()? SUM( SELECT( Orders[Total Amount], AND( ([Customer] = [_THISROW].[Customer]), ([Order Date] >= [_THISROW].[Order Date]) ) ) ) Use SELECT() to gather values from a single column only. See also: AND(), SUM()

I think it summarizes what I tried earlier in two separate steps:

SUM(
  SELECT(
    Detail_Day[TotalTime_Detail_VC],
    AND(
      [key_detail_Day] <> [_THISROW].[key_detail_Day],
      [Related_Key_Detail] = [_THISROW].[Related_Key_Detail]
    )
  )

)

Anyway, I still have the cycle-error message.
I think I get the point, but I have no idea about how to get it working ? Any idea ?

(A second trouble I will probably have is matching duration vs time, but I will deal with it in a second time)

Many thanks in advance

[Steve · Accepted solution]

This?

(
  "008:12:00"
  - SUM(
    SELECT(
      Detail_Day[duration-column],
      AND(
        ([Related_Key_Detail] = [_THISROW].[Related_Key_Detail]),
        NOT([_ROWNUMBER] = [_THISROW].[_ROWNUMBER])
      )
    )
  )
)

replacing duration-column with the name of the column in the Detail_Day table that contains the duration associated with each row.

[Aurelien]

This.
Is.
Extraordinary.

Thanks a lot !

[Aurelien]

I just noticed some interesting point: if I try to separate your formula in pieces, as I did before, I get the cycle formula error, but not when everything is in the same calculation.
That’s curious…

[Steve]

If column 1 computes its value using the value of column 2, and column 2 computes its value using the value of column 1, you get a cycle, because each column requires the other to have a value before it can compute its own. That’s what you’re running into.

[Aurelien]

Ow, said this way it’s ridiculously simple

Thanks for this explanation !