Controlling Virtual Column Vs. App Formula

[Rurrea]

Can you limit a virtual column from running based off the value in a column.

For instance: In my Orders Table

We have these tables:

Order Details

Deliveries

Accessories

Payments

ETC...

Virtual Column:

Total Subtotal Due:     SUM ( SELECT (Order Details[Total], [Order Id] = [_THISROW].[Order Id]))+SUM(Select(Deliveries[Total Delivery Fee], [Order Id] = [_THISROW].[Order Id]))+SUM(Select(Accessories[Total Cost], [Order Id] = [_THISROW].[Order Id]))+SUM(Select(Furniture[Selling Price], [Order Id] = [_THISROW].[Order Id]))+SUM(Select(MCO[Selling Price], [Order Id] = [_THISROW].[Order Id]))-SUM(Select(Customer Payments[amount], [Order Id] = [_THISROW].[Order Id]))

Can the above virtual column only run on Rows in my Orders Table that have a status of "Open"

Or is it much better to write a app formula in a new column. If so, how can i make the app formula recalculate if anything is edited in any of the reference table in that order.

[Joseph_Seddik]

SELECT(table[column], 
  AND(
    condition 1,
    condition 2,
    "Open" = [status]
  )
)
____

App formulas will recalculate for one row upon the change in any column in this row. 

Additionally, virtual columns will calculate for all rows with every sync. 

[Rurrea]

Applying that expression in the "Reset on edit".

However i need it to recalculate whenever other references in that order table are changed, row added, row deleted.

Reasoning for looking for another option versus the current virtual columns i have, is that i have over 30K rows in each table. Business has grown and the data is growing quickly.

How can I do that?

App formulas will recalculate for one row upon the change in any column in this row

Im hoping this applies to related reference tables in that row as well.

[Joseph_Seddik]

I'd say try to avoid the use of virtual columns for this purpose. 

First you should create a slice for every table based on [status] = "open" and any other criteria that can help filtering your table. 

Also, make use of Actions and Automation to recalculate the desired columns. This will involve a lot of work from you but will make your app run faster. 

In one of my apps I needed to calculate the sum in different tables, but instead of using SUM(SELECT .. I employed actions and bots that will launch upon the addition of a new row to update the corresponding total, along with automated checks to ensure data integrity, all this without a single virtual column.