Sum of numbers from SharePoint list columns

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Dear all,

I have SharePoint list columns as below. The default value of each columns are:

FRR1 = 101, FRR2 = 102, FRR3 = 103, FRR4 = 104, FRR5 = 105

total rating.PNG

In PowerApps, I displayed the data in Gallery view. I wanted to add all numbers from FRR1 until FRR5 columns and store it in Total Rating column. The app will sum up all numbers in the columns (that are not default value).

On OnSelect button property, I put below code. However, it does not sum up all items correctly. As you can see on Total Rating column above, the result are all "3". Any solutions to solve this?

ForAll(RenameColumns(Gallery1.AllItems, "ID", "IDD"),
 Patch('List Name', LookUp('List Name', ID = IDD),
 {
 'Total Rating': Sum(
 LookUp('List Name', FRR1 <> 101, FRR1),
 LookUp('List Name', FRR2 <> 102, FRR2), 
 LookUp('List Name', FRR3 <> 103, FRR3), 
 LookUp('List Name', FRR4 <> 104, FRR4), 
 LookUp('List Name', FRR5 <> 105, FRR5))
 }))

Thank you in advance!

Categories:

Building Power Apps

All responses (4)

Answers (1)

Sort by

v-qiaqi@microsoft.com Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi@shabilass,
Based on the issue that you mentioned, do you want to sum every row from FRR1 until FRR5 and store the summed result in the 'Total Rating' column?
Could you please share a bit more about the scenario, I am quite not clear about your formula:
Do you want to sum all the default value or the real value within the corresponding column?
Why you need a ForAll() function?
If my understanding is correct, you want to add all numbers in a row. If so, you just need to use the Sum() function as below:
Set the Text property of the Label represents the 'Total Rating' column as below:

Sum( ThisItem.FRR1, ThisItem.FRR2, ThisItem.FRR3, ThisItem.FRR4, ThisItem.FRR5 )

Note: I assume that all the columns are Text types.
Hope it could help.
Best Regards,
Qi
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @v-qiaqi-msft

1. Do you want to sum all the default value or the real value within the corresponding column?
- I wanted to sum all the real value of each item and store it in Total Rating column. If the column is not yet updated into real value, it will not sum it up.

2. Why you need a ForAll() function?
- I used ForAll() to update "Total Rating" of all items in the gallery at the same time once the Submit Button in PowerApps is clicked. Correct me if I'm wrong.

Maybe I can add a Label in the gallery and set the Text as you mentioned and patch it into SharePoint list to update the column. Now, any ideas to sum up the real value only?

Thank you.
Verified answer

v-qiaqi@microsoft.com Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
Hi@shabilass,
Do you want to sum the real values rather than the default values in these 5 fields?
Follow your lead, you want to define if the current value within the field is a real one firstly. If it is, adds the number into the sum operation, otherwise, do not add.
I do not know that you have a form to submit, as such that you need to try the following workaround.
Set the Default property of the TextInput control corresponding to the 'Total Rating' column as below:
Sum( If( ThisItem.FRR1 <> "101", ThisItem.FRR1 ), If( ThisItem.FRR2 <> "102", ThisItem.FRR2 ), If( ThisItem.FRR3 <> "103", ThisItem.FRR3 ), If( ThisItem.FRR4 <> "104", ThisItem.FRR4 ), If( ThisItem.FRR5 <> "105", ThisItem.FRR5 ) )
Set the Update property of the data card as below:
DataCardValue19.Text
Note: DataCardValue19 represents the TextInput control corresponding to the 'Total Rating' column.
Hope it could help.
Best Regards,
Qi
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you so much for helping me. It's working well!