SUM column based on Currency

[RvdHeijden]

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

[LaurensM]

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]

@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]

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]

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]

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

[WarrenBelz]

@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

[LaurensM · Accepted solution]

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]

@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]

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