Sum data table column

[clmile]

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. 

[v-monli-msft]

Hi @clmile ,

Try below formula instead:

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

Regards,

Mona

[clmile]

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

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]

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

@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 · Accepted solution]

@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")

[Er-PiTeam]

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!