cancel
Showing results for 
Search instead for 
Did you mean: 

Ability to perform math on columns in Data table

It would be great if we could perform math functions (like SUM) on columns of data in a Data table.

Status: Completed

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.

Comments
PowerApps Staff
Status changed to: Under Review
 
Level: Powered On

Any update on this?

PowerApps Staff
Status changed to: Completed

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.

Level: Power Up

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.

PowerApps Staff

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.

Level: Power Up

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.

Level: Powered On

@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:

 

AddColumns(
    bubbledata,
    "X-X average",
    'Cost per m2 ' - X_Average,
    "Y-Y average",
    'Total Area' - Y_Average,
    "X-X average*Y-Y average",
    X_Average * Y_Average
)

Any assistance would be of great help-thank you Smiley Happy

Level: Power Up

@MaxDonen, 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.

Set(gTotal,Sum(MyCollection,Num))

Level: Powered On

Thanks @Jeff_Ballard -that idea worked for what I was trying to sort! Much appreciated.