Answered

Sum column based on criteria Sharepoint list

(0) Share

Report

Posted on by JamtMaster

140

Hello, I am using the code that you see in the image to try to obtain the amount of available points of the employees by adding the values of the "Disponible" column of the sharepoint list called "Ideas" however, when doing so, 8 are added additional zeros to the real number.

Attached photo where it is displayed that in the gallery the value is correct as 750.00 but in the gallery and in the label 75000000000 is displayed. The available column is an automatic calculation column, I do not know if that influences something, how could I solve this?

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Sort by

RandyHayes 76,299 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!
@JamtMaster

Please consider changing your Formula to the following:

Sum(Filter(Ideas; Codigo=txtemployee.Text); Value(Disponible))

I hope this is helpful for you.

Was this reply helpful? Yes No
JamtMaster 140 on at

Like (0)

Report
Copy link

Link copied!
@RandyHayes
Hi, try but the same result. Looking at a similar case, it occurred to me to "fix it" with this:

Substitute(Sum(Filter(Ideas;Codigo=txtemployee.Text);Disponible);Right(Sum(Filter(Ideas;Codigo=txtemployee.Text);Disponible);12);"")

But I am convinced that it is very likely that an error will occur later and it seems strange to me, I do not understand where you get those 12 additional zeros from if both the HappyPuntos field and the Canjeado field and the Disponible column place them as numbers with 2 decimal places, I even put those fields as decimal 1, 0 and automatic but the same thing happens

Edit: The solution was not effective, I changed the amount in the Disponible column and a person who had 10 appeared as if he had 100, so if he did it with the substitute, he would have to consider all the scenarios up to 90,000, which is the largest amount.

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@JamtMaster

The formula should have worked for you.

The thing is this...a calculated column ends up as a text-like value in the list, even though it might be defined as a number. You can change the column format a thousand different ways and it will always result in the same, simply because it is a calculated column.

So, you have to take the Value of it in the app to get the actual value.

You really don't want to get into this Substitute and Right formulas as this is only going to bomb on you in some scenario. Not to mention, you're not casting the types from string to number in the formula, so PowerApps is left to assume it.

I am not at a point where I can check into the specifics at the moment, but know that the formatting in the list settings will have no impact on the results in the app. It is stored in a text-like way, so it needs to be converted to a Value with that function.

If that doesn't lead you to the right place, post back and I will check into it as soon as I can.

Was this reply helpful? Yes No
Verified answer

JamtMaster 140 on at

Like (1)

Report
Copy link

Link copied!
I finally managed to fix it this way:
Sum(Filter(Ideas; Codigo=txtemployee.Text); HappyPuntos-Canjeado)
For some reason the available field always added zeros so I performed the operation in the same code and the result is the desired one

Was this reply helpful? Yes No