web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps / SUM column based on Cu...
Power Apps
Answered

SUM column based on Currency

(0) ShareShare
ReportReport
Posted on by RvdHeijden 602

Gooddday,

 

Ive got a question based on a simple currency column in my SP list.

 

Ive got a column which is called 'Schouwwaarde Complex' and sometimes it has a value and sometimes it does not.

Now I want a label which calculates the total amount for that column but here comes the tricky part.

 

The value in that column uses a formula in TEXT in order to display a currency in my Gallery
"€ " & Text(Value(Round(Substitute(ThisItem.'Schouwwaarde Complex';".";",");2));"[$-nl-NL]#.###,00";"nl-NL")

 

When I use the formula (in the label) 

Sum(Gallery1.AllItems;'Schouwwaarde Complex')

 

The Result is 631077500000000000 (not sure how many 0's but a lot)

So basically I need to do something with the formula to display a total amount in €  so either I have to do something with VALUE or a totally different formula

 

 

Categories:
Building Power Apps
  • LaurensM Profile Picture
    LaurensM 12,516 Moderator on at

    Hi @RvdHeijden,

     

    Do you want to provide the total of the sum function with the same logic (currency layout) as your gallery labels? If so you could use the formula below in the Text property of that label:

    $"€ {Text(Sum(Gallery1.AllItems;'Schouwwaarde Complex');"[$-nl-NL]#.###,00";"nl-NL")}"

     

    If this solves your question, would you be so kind as to accept it as a solution.

    Thanks!

  • RvdHeijden Profile Picture
    RvdHeijden 602 on at

    @LaurensM ive tried using your formula and the result is € 75.757.750.000.000.000,00 and my result is good but not that good 🙂 

    In my gallery (when I filter a certain project) the result should be € 8.665,50 but this formula returns € 4.992.600.000.000.000,00

  • LaurensM Profile Picture
    LaurensM 12,516 Moderator on at

    Hi @RvdHeijden,

     

    The label will sum the 'Schouwwaarde Complex' value of all the records in gallery 1. When filtering the gallery, the value displayed will dynamically change. In other words, the Sum() value should always match total (Shouwwaarde) of the displayed rows (as defined in your database).

     

    However, be careful when substituting the dot with a comma and then using the Value() function (formula for the gallery item schouwwaarde currency). In my example, this turned 12.50 into €125,00. To avoid this, remove the Value() function from the formula:

     

    "€ " & Text(Round(Substitute(ThisItem.'Schouwwaarde Complex';".";",");2);"[$-nl-NL]#.###,00";"nl-NL")

     

    Now as a possible (not so pretty) alternative to my previous code, you could add an invisible label in the gallery with the Text property as ThisItem.'Schouwwaarde Complex' - let's call it lblComplexInvis.

    The total value formula would then reference the lbl instead:

    $"€ {Text(Sum(Gallery1.AllItems; Value(lblComplexInvis.Text));"[$-nl-NL]#.###,00";"nl-NL")}"

     

    I hope this helps!

  • RvdHeijden Profile Picture
    RvdHeijden 602 on at

    I've changed the text value of Schouwwaarde Complex to:

     

    If(ThisItem.'V&P Schouw aangeleverd'<>Blank(); "€ " & Text(Round(Substitute(ThisItem.'Schouwwaarde Complex';".";",");2);"[$-nl-NL]#.###,00";"nl-NL"))

    And it seems like the same result is given so that is ok I guess...

     

    and then the text value for the label (that SUMs the total value) to:

    $"€ {Text(Sum(Gallery1.AllItems;'Schouwwaarde Complex');"[$-nl-NL]#.###,00";"nl-NL")}"

     

    But the result is the same, it shows a whole lot of zero's.

  • RvdHeijden Profile Picture
    RvdHeijden 602 on at

    @LaurensM or @WarrenBelz any ideas on how to fix this ?

  • WarrenBelz Profile Picture
    WarrenBelz 156,732 Most Valuable Professional on at

    @RvdHeijden ,

    I have seen this bug mentioned before - seems to haunt European syntax. I cannot replicate it here (Australia), so am not sure how to fix, but you could try this

    With(
 { 
 wSum:
 Sum(
 Gallery1.AllItems;
 'Schouwwaarde Complex'
 )
 }
 "$" & 
 Text(
 Round(
 wSum;
 2
 );
 "[$-nl-NL]#.###,00";
 )
)

     

    Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

    MVP (Business Applications)   Visit my blog Practical Power Apps

  • Verified answer
    LaurensM Profile Picture
    LaurensM 12,516 Moderator on at

    Hi @RvdHeijden,

     

    My apologies for the delay in replies. As stated by @WarrenBelz, it was difficult to replicate this issue with the US coding syntax.

     

    I did some tests by also using the Dutch Power Apps coding syntax and ran into the exact same issue - as explained by you. I was able to resolve the Sum() error with the following 2 options:

     

    (1) Sum the values by referencing the label in which you show the values in euro. I mean the label that used to have the following formula in the Text property:

    "€ " & Text(Value(Round(Substitute(ThisItem.'Schouwwaarde Complex';".";",");2));"[$-nl-NL]#.###,00";"nl-NL")

     

    By using the other syntax I now realize that this code was ok and Value / Substitute was not an issue (ignore my previous comment). You can simply sum the values of these labels by referencing the label name (in my example I named it Label1):

    Sum(Gallery1.AllItems;Label1)

     

    (2) Alternatively, you can use the similar Value & Substitute structure in the Sum formula:

    Sum(Gallery1.AllItems;Text(Value(Round(Substitute(ThisRecord.'Schouwwaarde Complex';".";",");2))))

     

    You will have to apply the same logic that you used in your gallery (to display the value correctly) in the Sum() formula as well - either by referencing the Label or rewriting the formula in the Sum() function. Simply using Sum(Gallery1.AllItems;'Schouwwaarde Complex') leads to a lot of 0's - this also happened during my tests.

     

    If this solves your question, would you be so kind as to accept it as a solution.

    Thanks!

  • RvdHeijden Profile Picture
    RvdHeijden 602 on at

    @WarrenBelz at first your formula resulted in an error, something about have 2 arguments when expecting 3 so I've altered it a bit but I'm not sure if I did it correctly because the result is still a very big number with a lot of zero's (€ 120.621.000.000.000.000,00)

     

    With(
 { 
 wSum:
 Sum(
 Gallery1.AllItems;
 'Schouwwaarde Complex'
 )
 };
 "€ " & 
 Text(
 Round(
 wSum;
 2
 );
 "[$-nl-NL]#.###,00"
 )
)

     

  • RvdHeijden Profile Picture
    RvdHeijden 602 on at

    Can't believe this first solution was that simple, I've changed the formula back to VALUE like this

    If(ThisItem.'V&P Schouw aangeleverd'<>Blank(); "€ " & Text(Value(Round(Substitute(ThisItem.'Schouwwaarde Complex';".";",");2));"[$-nl-NL]#.###,00";"nl-NL"))

     

    and the text value of the label (that sums these values) to  

     

    "€ " & Text(Value(Round(Substitute(Sum(Gallery1.AllItems;Schouwwaarde);".";",");2));"[$-nl-NL]#.###,00";"nl-NL")

     

     and now it works, I've double checked some outcomes and the add up so problem solved

     

    @WarrenBelz and @LaurensM  thanks for your help 

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities

Quick Links

Season of Sharing Community Challenge Winners!

Congratulations to our community stars!

Kudos to our 2025 Community Spotlight Honorees

Expanding mentorship, skilling, and AI innovation

Congratulations to the August Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
11manish Profile Picture

11manish 354 Super User 2026 Season 2

#2
M Bilal Khan Profile Picture

M Bilal Khan 330

#3
Mohsin Ali Profile Picture

Mohsin Ali 232

Last 30 days Overall leaderboard

Featured topics

Auto-Populate field Power Pages
Gallery Tabs - Show or Hide Tabs based on Field Value
How to patch multiple records at once
Enable Copilot for end users in model-driven apps
Question for everyone : How are you using Create Text GPT in AI Builder?
Community content - sample components, blogs etc. - Link to this page (http://aka.ms/PCFdemos)
Welcome to the Power Apps forum!

Product updates

Microsoft Power Platform Community release plans