How to get Average of the last three row value

[111139 (กรมจเรทหารเรือ กองทัพเรือ)]

i want to make and Format view to warn me if the Average of the last three rownumber price value is over 100 $

what should be the expression for the virtual colume

Thank you very much

[Steve (Steve Coile)]

Probably best to use a regular (non-virtual) column to store the result of the test (the expression below), as a virtual column for this will greatly slow sync times. The format rule can just check this column’s value.

(
  AVERAGE(
    SELECT(
      table[price-column],
      IN(
        [_ROWNUMBER],
        TOP(
          SORT(
            SELECT(
              table[_ROWNUMBER],
              ([_ROWNUMMBER] < [_THISROW].[_ROWNUMBER])
            ),
            TRUE
          ),
          3
        )
      )
    )
  )
  > 100.0
)

See also:

AVERAGE()

Format Rules: The Essentials

IN()

SELECT()

SORT()

TOP()