cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Larsvedoy
Helper I
Helper I

Sum a SharePoint calculated column - format issue?

I have a SharePoint list (Mål_1) with a calculated field (Score_sum_A), number, with Norwegian format (NB-no).

req01_01.jpg

Loaded into a table in PowerApps, the data looks like this:

req01_02.jpg

Using LookUp, I can get the first record, and by multiplying it with 1 I'm able to get a value i can use.

req01_03.jpg

But when trying to sum the column, I just get an empty result.

req01_04.jpg

Trying the same on the non-calculated column 'A-01-01' - seems to work fine.

req01_05.jpg

From this I'm assuming I can't sum a calculated column, and need to find a workaround. Is this correct? If so, what should be my course of action?

 

Lars

15 REPLIES 15
v-xida-msft
Community Support
Community Support

Hi @Larsvedoy ,

Actually, it is an known issue with Calculated field in SharePoint List -- When loading Calculated field value from SP List into canvas app, the value would be appended with 13 decimals.

Please check the following thread for more details:

https://powerusers.microsoft.com/t5/Building-Power-Apps/Calculated-SharePoint-column-giving-13-decim...

 

In addition, the Calculated field value would be recognized as Text type value when loading from SP List into canvas app. For your issue, please consider modify your formula as below:

Sum(
    Filter(Mal_1; Ide_ID = 50);
    Value('Score_sum_A')
)

or

Sum(
    Filter(Mal_1; Ide_ID = 50);
    Round(Value('Score_sum_A'); 1)
)

 

Please take a try with above solution, check if the issue is solved.

 

Best regards,

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

Thanks Kris,

 

There's no change when adding Value() to my formula. And here is the interesting part... If I use the lookup - without Value(), I do get a result:

Skjermbilde1.PNG

 

...but when I add Value() it's empty

 

Skjermbilde1-1.PNG

 

yashag2255
Dual Super User II
Dual Super User II

Hi @Larsvedoy 

 

Can you try to update the expression to:

Sum(AddColumns(Filter(Mal_1;Ide_ID = 50),"ValueToSum", Value(Score_Sum_A)), ValueToSum)

Here, we created an additional column named "VaueToSum".

 

Hope this helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

I'm still having the same result...

 

Skjermbilde1-2.PNG

 

Lars

Hey @Larsvedoy 

 

I am guessing that the Ide_ID is a text field and you are passing a number there. Can you try:

Sum(AddColumns(Filter(Mal_1;Ide_ID = "50"),"ValueToSum", Value(Score_Sum_A)), ValueToSum)

Also, if this does not help, can you hover over the blue underline and share the error message that you are getting.

 

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

So, you are right about the text - changing it to "50" got rid of the incompatible type message. But there's still no value...

 

Skjermbilde1-3.PNG

 

Thanks,

 

Lars

Hi @Larsvedoy 

 

I carried out a test on my side and the expression works fine. Can you try to save the app, close the browser, clear the cache and open it again to confirm if you are still not able to see the values?

 

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Thank you @yashag2255 for your help!

 

I did this - and still no result. It seems apparent its a format issue:

 

I make three labels

 

lbl1 = LookUp(Mål_1;Delmål="test";Score_sum_A) shows 0.666666666667

lbl2 = LookUp(Mål_1;Delmål="dfad fad";Score_sum_A) shows 3.0000000000

lbl3 = lbl1 + lbl2 shows nothing

 

Then if I change it to:

 

lbl1 = LookUp(Mål_1;Delmål="test";Value(Score_sum_A)) shows nothing

lbl2 = LookUp(Mål_1;Delmål="dfad fad";Value(Score_sum_A)) shows nothing

lbl3 = lbl1 + lbl2 shows nothing

 

But if I change it to

 

lbl1 = LookUp(Mål_1;Delmål="test";Score_sum_A)*1 shows 0,66666667

lbl2 = LookUp(Mål_1;Delmål="dfad fad";Score_sum_A)*1 shows 3

lbl3 = lbl1 + lbl2 3,6666667

 

I think I've tried all combination of Text() and Value() - but no results...

 

Lars

 

Hi @Larsvedoy 

 

What is the formula used for the Calculated column and what is its type?

Can you check if the below expression works:

Sum(AddColumns(Filter(Mal_1;Ide_ID = "50");"ValueToSum";Value(Text(Score_Sum_A))); ValueToSum)

 

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

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!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,167)