It would be great if we could perform math functions (like SUM) on columns of data in a Data table.
Marking this request as completed with the experimental release announced in https://powerapps.microsoft.com/en-us/blog/improved-data-table-control-with-column-formulas/. Please reach out with feedback on this improved control behavior.
Any update on this?
I may need a more complete explanation of the experimental tools, but I do not see how any of the new features allow us to sum a column of data in a table.
In my case I use AddColumns to create a new column with a calcualted number of days. Now I need to sum the number of days in each row to give a total number of days.
To help clarify, were you able to do this already with a Gallery control? Or is the request also not supported there.
The improved data table control allows changing a column from binding to a specific field and instead get the value from a formula. At this time you can't add a new column directly but by changing the Text property (only in formula bar) and Header Text property you can make the column indepdendent of that field. In the future we plan to add a direct option to create a custom column (like the Form's custom card). The custom column formula could retrieve an aggregate value from another source to show in the data table.
Based on your reply, this might be re-activated as an open request or I need to clarify how to use the custom column more.
I am able to use AddColumn function to create a new coulmn for the table that is not bound to a field and use a math formula to calculate a value in that column for each row. I am looking for the ability to do a sum of all the values in the new column.
A work around I just came up with today is to use the same filter/add column statement to create a collection and to create the table. I use the sum function to get the total from the collection and the table to dipslay the individual rows to the user. This is not optimal as the powerapp has to query the SharePoint list twice.
I also think this post should not be marked as solved. The original post said, "It would be great if we could perform math functions (like SUM) on columns of data in a Data table". I do not see anything in the experimental release that allows someone to do a SUM on a column of data in a table.
@Jeff_Ballard Thanks for this, which sounds like a potentially usable workaround for my own scenario. To date, this issue remains unresolved as far as I am aware. Could I please ask how you were able to add a collection via the filter/addcolumn statement? I am keen to implement something similar. I am currently using the following statement to import an Excel table, Bubbledata, into a datatable, and then add 3 columns to it:
'Cost per m2 ' - X_Average,
'Total Area' - Y_Average,
"X-X average*Y-Y average",
X_Average * Y_Average
Any assistance would be of great help-thank you 🙂
@Anonymous, in the OnVisible function for the screen I included the statment below to create the collection and add a "Num" column to it. Sorry it is a bit complex as there no built in NetWorkingDays function which is what I needed to calculate the "Num" value for each row in the collection. Table1 is a list of holidays to exclude from the working days calculation:
ClearCollect(MyCollection,AddColumns(Filter('Time Away List',Approved.Value ="Approved",Title=User().FullName),"Num",If(DateDiff('Start Date','End Date')<7,Mod(5+ Weekday('End Date') - Weekday('Start Date'),5)+1- CountIf(Table1, Date >= 'Start Date', Date <= 'End Date'),(RoundDown(DateDiff('Start Date','End Date')/ 7, 0)) * 5 + Mod(6 + Weekday('End Date') - Weekday('Start Date'),5)- CountIf(Table1, Date >= 'Start Date', Date <= 'End Date'))));
Then I use the following statement to sum up the "Num" values and store the result in a global variable that is used to dispaly the result in a label.
Thanks @Jeff_Ballard -that idea worked for what I was trying to sort! Much appreciated.