How to format number fields to currency, but then also Sum them?

[DanaBananaNZ]

Hi all!

I have some calculated number fields in a Gallery that i'd like to be formatted to currency.  This works fine, however as it converts the number to Text, then I can't seem to sum those fields to create a Total Field.  The only work around I can currently find is to duplicate the fields and display one with Text formatting, and Sum the other field that I will hide.  This seems a bit silly, as surely there is a better way!

My calculated field is called 'Total Sell by Area calc' and the Gallery is called 'AreaSummaryGal'.

The calculated fields listed in the gallery return values of eg: 4485.888555.  This is messy, so i used the Text (Value,"[$-en-US]$#.##").  However, as this converts it to a text, my Total field doesn't show anything!

The formula i used for the Total field is:
Text(Sum(AreaSummaryGal.AllItems,'Total Sell by Area calc'),"[$-en-US]$#.##"). 

This Sum only works when i take the formatting off the 'Total Sell by Area calc' field.

Any ideas?

[WarrenBelz]

Hi @DanaBananaNZ ,

You should be able to reverse the process using Value( text field).

Please click and accept as Solution if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a Thumbs Up.

[DanaBananaNZ]

It is already a value, so I'm not sure how this would format my field?

[PowerAddict]

Are you using this just to display your calculated field?
Text (Value,"[$-en-US]$#.##")

Then are you using this calculated field to get the sum using this formula?
Text(Sum(AreaSummaryGal.AllItems,'Total Sell by Area calc'),"[$-en-US]$#.##")

Also, are you saying when you keep the calculated field to Value, the sum doesn't work?

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution.

Thanks!
Hardit Bhatia
The Power Addict
https://thepoweraddict.com

[DanaBananaNZ]

Thanks for your reply!  In answer to your q's:

I am using the Text format of what I put in the posted in my first post.. not sure why i can't see it now.  It looks like you have copied it correctly though..

When i format the calculated field to Text (ie to make it look tidy and give it a $ symbol), the sum doesn't work.  If i take off the formatting completely (ie and leave it as the original format which is a number), the Sum does work!

[PowerAddict]

Have you tried this?

Text(Sum(AreaSummaryGal.AllItems,Value('Total Sell by Area calc')),"[$-en-US]$#.##")

Let me know if this helps.

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution.

Thanks!
Hardit Bhatia
The Power Addict
https://thepoweraddict.com

[DanaBananaNZ]

Yes i have tried that but as it is already a value, i don't think that it can convert a value to a value!!  

(Error message says expected text here)

[PowerAddict]

And what error message do you get with the original formula for sum?

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution.

Thanks!
Hardit Bhatia
The Power Addict
https://thepoweraddict.com

[PowerAddict]

Let me know your use case is different than my example.

I have a collection which is defined as:

ClearCollect(ColTest, 124.343, 23543.23, 223.23123)

I then have a label (instead of using a gallery, just for a quick example) that displays the first record of the collection in a formatted format. The Text property of the label is set to:

Text(First(ColTest).Value, "[$-en-US]$#.##")

The result is: $124.34

I have another label to display the sum of all values in the collection. The Text property of this label is set to:

Text(Sum(ColTest, Value), "[$-en-US]$#.##")

The result is: $23890.8

Which is what is expected. So let me know whats diff in your case, and we can figure this out. 

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution.

Thanks!
Hardit Bhatia
https://thepoweraddict.com

[DanaBananaNZ]

Thank you for trialling it for me and for trying to help.  Unfortunately this code is exactly the same as I have used, which I have mentioned in my first post.  

I don't get an error message, the Sum field is just blank.