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.
Solved! Go to 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")
Hi @clmile ,
Try below formula instead:
Sum(Filter(<same filter as data table>),Amount)
Regards,
Mona
@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 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.
@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?
@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")
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!