Answered

Question on how to display sum of only filtered table results

(1) Share

Report

Posted on by usoneillb

I'm trying to get the sum of a column called "Hours Removed" in power apps to display the sum of the "removed hours" for associates listed as I filter the table by date range or by associate.

My table filters correctly as I need to by the date range or by associate name and list the hours already for each associate. I just need to add in a display to show the sum of the "Hours Removed" column of the filtered table data.

Below is the table formula that I filter by that looks at the Associates name in the table and a date range formula that works well to sort the table .

Any suggestion though on how to also have a text box to show the sum of hours of the "Hours Removed" column that are filtered in this table " "DataTable_Validations_Register"

Below is the table formula that I filter by that looks at the Associates name in the table and a date range formula that works well to filter the table data.

Sort(Filter(
    'Training Validations Correction Register',
    ThisRecord.Date >= DatePicker_From.SelectedDate,
    ThisRecord.Date < DatePicker_To.SelectedDate + 1  &&
     (
        IsBlank(txtSearchAssociate.Text) || 
        StartsWith(Associate.DisplayName, txtSearchAssociate.Text)
    )
),Date,SortOrder.Descending)

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (2)

Verified answer

WarrenBelz 153,079 Most Valuable Professional on at

Like (0)

Report
Hi @usoneillb,

Firstly, you can set the Default of txtSearchAssociate to "" (empty string) and save a bit of code (you do not need to test for Blank), but that is not your question. As Sum() may not Delegable (you did not state your data source), you can do this on a Label outside the Gallery.
With( { _Data: Filter( 'Training Validations Correction Register', Date >= DatePicker_From.SelectedDate && Date < DatePicker_To.SelectedDate + 1 && StartsWith( Associate.DisplayName, txtSearchAssociate.Text ) ) }, Sum( _Data, 'Hours Removed' ) )

You may also want to try
Sum( YourGalleryName.AllItems, 'Hours Removed' )

but a trap here with a Delegable filter in the gallery (as in yours) is that initially only the first 100 records are displayed and the user has to scroll down to get each 100 batch (so the total may not be initially correct).

I also assume here that Date (not a good name for a field) is a Date + Time field (hence the testing for the next day)

Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
MVP (Business Applications) Visit my blog Practical Power Apps LinkedIn

Was this reply helpful? Yes No
Verified answer

Infamous_Let 330 on at

Like (1)

Report

To display the sum of the "Hours Removed" column based on your filtered table, you can use the Sum function in a text label. Add a text label where you want the total to appear and set its Text property to the following formula:

Sum(
Filter(
'Training Validations Correction Register',
ThisRecord.Date >= DatePicker_From.SelectedDate &&
ThisRecord.Date < DatePicker_To.SelectedDate + 1 &&
(
IsBlank(txtSearchAssociate.Text) ||
StartsWith(Associate.DisplayName, txtSearchAssociate.Text)
)
),
'Hours Removed'
)
This formula filters your table using the same conditions you use to display the table data, then calculates the sum of the "Hours Removed" column from the filtered results. This will dynamically update the total whenever you change the date range or associate filter.

Was this reply helpful? Yes No
usoneillb 14 on at

Like (0)

Report

Thank you all thank worked. I went with the one Infamous Let suggested. I really appreciate everyones help.

Was this reply helpful? Yes No