cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
clmile
Frequent Visitor

Sum data table column

All of my data comes from ShrePoint lists. I have a SelectionGallery that show me a list of items to choose from and then another DetailsGallery that filters the data based on the selection in the SelectionGallery. I then have a Data Table that is also filtered based on what is selected in the Selection Gallery. What I need to do now is sum the Amount field in the Data Table based on the filtered data. Sum(SPList,'Amount') works but it sums up everything not just what is filtered. I have tried doing a Sum(Filter(<same filter as data table>),SPList,'Amount') and I get the yellow triangle but the field stays blank. 

1 ACCEPTED SOLUTION

Accepted Solutions
clmile
Frequent Visitor

@Er-PiTeam  Thanks for the suggestion on that feature I loved that. It did make something click in my head that I had not considered before. The column in SP that I was filtering on was a lookup field to another list so I changed it to a choice field and it works perfectly now. I feel a little dumb since I have spent so much time on this one field. My original formula did actually work and I had to add some formatting at the end so the currency looked right

 

Text(Sum(Filter('SP List','<same filter as the data table>'),'Invoice $'),"[$-en-US]$#,###.00")

View solution in original post

7 REPLIES 7
v-monli-msft
Community Support
Community Support

Hi @clmile ,

 

Try below formula instead:

Sum(Filter(<same filter as data table>),Amount) 

Regards,

Mona

Community Support Team _ Mona Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-monli-msft I've tried this as well and it still doesn't not work. Again I get the yellow triangle but nothing populates in the field. 

Er-PiTeam
Frequent Visitor

Are you able to give some more details to give us an of how your gallery collection is changing? At first glance, I think a Sum with the filter will work, but I am also doing a similar exercise, let me know if this case is similar to yours:

1. I have a collection of records GROUPED by columns, in this grouping, I am also getting the total number of records in that group (in your case, the count would be a sum).
2. When the user clicks an item in my first gallery, that group of records associated will then be displayed in my second gallery (kind of like yours). And then finally, they click that drilldown record and they move on to that one record.
3. I also have a table that groups and sums these records as well by a status (which is the same thing as your filtered).

I think you can work with a sum Filter combo, or a group by, hard to say without seeing how you have structured your workflow.

See if something like this helps:
ClearCollect( colGrouped, AddColumns,GroupBy(Filter(Source,<same filter as data table>), "Amount","grpSPLists"),"Total", Sum(grpSPLists.Amount))

That gets you grouped up data set, but you can also use this in conjunction with UnGroup to seperate them back out. But, again, if you're just using a label or something to display the sum of an existing collection or datasource where all you need is a filter, then Sum filter combo should work,

The fact that you are getting a yellow triangle means that what you are doing is not totally wrong altogether, for example, something as simple as adding Value(Amount) might do the trick,
clmile
Frequent Visitor

@Er-PiTeam I have tried both the label and text input fields to display the sum and all I get is the yellow triangle. the way my app is set up is that I have a SelectionGallery that lists all of my contracts and when you select on a specific contract the DetailsGallery is filtered to provide the details for just that one contract. I also have a data table that is being used to display the invoices for the contracts and also filters based on the contract that is selected. One of the fields in the table is the amount of the invoice. What I'm trying to do is SUM that amount field.

 

I tried your ClearCollect formula and I still only get the yellow triangle but nothing populates in the field.

Er-PiTeam
Frequent Visitor

@clmile , by any chance, do you have this expirmental feature turned on: Enable Formula Bar Result View,

 

The fact that you are getting a yellow symbol means that there is some sort of delegation rule, or formula conversion/formatting issue (from my experience).   If you have that feature enabled, youll be able to get a better sense of what your break is.

 

I get the feeling that your collections are either empty (which means that you might want to handle that), or the data in your SUM column, cant actually because its not reading numbers. 

 

What does your app checker say in regards to this formula?  What is being highlighted in your formula when you get the triangle?

 

 

clmile
Frequent Visitor

@Er-PiTeam  Thanks for the suggestion on that feature I loved that. It did make something click in my head that I had not considered before. The column in SP that I was filtering on was a lookup field to another list so I changed it to a choice field and it works perfectly now. I feel a little dumb since I have spent so much time on this one field. My original formula did actually work and I had to add some formatting at the end so the currency looked right

 

Text(Sum(Filter('SP List','<same filter as the data table>'),'Invoice $'),"[$-en-US]$#,###.00")

View solution in original post

Er-PiTeam
Frequent Visitor

Haha! @clmile  Np friend!  When I turned this feature on, it honestly expanded my knowledge and thinking on how I construct collections and transform them throughtout my app!

 

It can do tables, colors, formulas!  It's pretty neat!  Glad you like it!  I cant work without it!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (1,216)