Sum a SharePoint calculated column - format issue?

[Anonymous]

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

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

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

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

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

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

[v-xida-msft]

Hi @Anonymous ,

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-decimals-when-imported/m-p/399672

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,

[Anonymous]

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:

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

[yashag2255]

Hi @Anonymous 

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!

[Anonymous]

I'm still having the same result...

Lars

[yashag2255]

Hey @Anonymous 

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!

[Anonymous]

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

Thanks,

Lars

[yashag2255]

Hi @Anonymous 

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!

[Anonymous]

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

[yashag2255]

Hi @Anonymous 

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!