advice on how best way to add calculations to a table based on a join

[Anonymous]

I am looking for the best way to add a bunch of columns to a "budgets" collection based on a  sharepoint table that is based on a bunch of calcualtions. The calculations are generated by  matching critera of the budget ID referenced from a trips table that I plan to create in sharepoint.  Currently the applicaiton is working in excel as the data source but I am looking to optimize/improve it as I port this over to sharepoint lists.

Trips Table has a few cols for expensed items (e.g. air, other, etc) that have an amount col and budgetID in it as well as a "status" columns to indicate if the status of the expense (e.g. estimated, actual) and approval state of the trip.

My challenges is what is the best way to add/calculate the budgets cols that does not impose too much overhead in the calculations.

My concern is that each column I am adding is performing a full sum operation of a another table multiple times and I need to add several columns like this that I put into a collection throughout my app. I think performing these type of calculations could take a long time since it seems powerapps does not support join so I am summing an entire table multiple times for one entry in a col. I am wondering if there are any suggestions on how sharepoint or preprocessing the data can help with the performance.

Here is an example of how what I am passing into Addcolums to add a dollar amount "_remaining" column for my budget table/collection. Note I use "_co" prefix for collections (coming from the data tables)

AddColumns( _coBudgets,  "_remaining" , BudgetLimit -

      (Sum(_coTrips, If ((Trim(Lower(Status))="approved" || Trim(Lower(Status))="completed") && _coBudgets[@ID]=AirBudgetID,
      AirAmt ))
     + Sum(_coTrips, If ((Trim(Lower(Status))="approved" || Trim(Lower(Status))="completed") && _coBudgets[@ID]=OtherExpBudgetID ,
     OtherExpAmt ))

In this example, I am summing columns in a "trips" table multple times  to get the final value of remaining. I consudering using filter but this expression is already getting pretty complicated.  

Thanks,

Gene

[v-siky-msft]

Hi @Anonymous ,

Do you want to improve the performance of your formula ? Though there is pre-calculated column in SharePoint, it is hard to deal with your complex logic. Also, I think there is no good way to improve the simplicity of the formula in powerapps.

Best regards,

Sik

[Anonymous]

Yes, I am wondering if I need to be concerned with the performance hit of needing to calcualte sums, etc on each term. I am looking to pre-filter the data so maybe it's not going to be a big concern. 

As far as using calcualted columns in sharepoint, I would definitely consider that but I do not know if Share point supports that. I noticed if I tried to do calcualtion in excel, the values are treated as statics. Do you know if sharepoint would maintain something like sum formula as I add entries to the table or if it can support a running Sum in a column outside of the application. I am new to a lot of the sharepoint features so may experiment.

Thanks,

Gene

[Anonymous · Accepted solution]

I think this this the solution I am looking for. It consolidates the number of times, each term in my column equation needs to perform a full calculation. I am aslo thinking filter may be more efficent than an IF statement in an Sum since Filters should be able to take advantage of and idexing in sharepoint that may be present. 

This approach performs the data filtering once and then uses if statements only on the items as needed to drive the  calculation logic. 

AddColumns( _coBudgets,  "_remaining" , BudgetLimit -

     "_NewRemaining",  BudgetLimit -
                 Sum(Filter(_coTrips, Trim(Lower(Status))="approved" || Trim(Lower(Status))="completed"),
                 If ( _coBudgets[@ID]=AirBudgetID, AirAmt) + If (_coBudgets[@ID]=OtherExpBudgetID, OtherExpAmt)
)

Previous example below  was summing the table columns for each term of the calculation field which introduces the more overhead. 

AddColumns( _coBudgets,  "_remaining" , BudgetLimit -

      (Sum(_coTrips, If ((Trim(Lower(Status))="approved" || Trim(Lower(Status))="completed") && _coBudgets[@ID]=AirBudgetID,
      AirAmt ))
     + Sum(_coTrips, If ((Trim(Lower(Status))="approved" || Trim(Lower(Status))="completed") && _coBudgets[@ID]=OtherExpBudgetID ,
     OtherExpAmt ))

I realize this is probably too specific to my application but I hope it helps someone that is looking to work with tables that foreign keys in it. My first problem was figuring out a way to refer tables in the foreign key refers to. After figuring that out, I became concerend with performance and delagation. Using the collections and filters helped to address that. Next I wanted to reduce the number of full table (e.g. sum) operations  in each term in my calculation.

I think the pattern above will apply to others that are using tables with relationships but if not, forgive the detailed sharing. 🙂

Thanks,

Gene