cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

Showing the result of a SUM from a column in a collection as a currency

Hi,

 

I have a label, where i am trying to show the total cost of works booked in.  The formula works and shows the correct value however i'm unable to get the value/text to show a a currency.  The label is subject to an IF Function and a SET variable, but just so you know, the syntax below fully works, except the fact that when the variable is set to "Price" it doesnt show the value as a currency, it simply just shows the number value,

 

If(VarFormat="Hourly", Sum(Filter(JobCollection,DateCalc=Text(WkEndingDate.SelectedDate-4,"[$-en-GB]yyyymmdd")),JobHours), 
If(VarFormat="Price",Sum(Filter(JobCollection,DateCalc=Text(WkEndingDate.SelectedDate-4,"[$-en-GB]yyyymmdd")),Text(JobPrice,"[$-en-GB]£#,###0.00"))))

Also if i take the Text Function out completely at the end then the result still works, but again just shows the value rather than any currency

 

Hope that makes sense.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Showing the result of a SUM from a column in a collection as a currency

Hi @d3ell 

I think the best solution is to just append a "£" symbol to the start of your value.

 

If(VarFormat="Hourly", 
   Sum(
      Filter(JobCollection,
             DateCalc=Text(WkEndingDate.SelectedDate-4,"[$-en-GB]yyyymmdd")),
      JobHours), 
   If(VarFormat="Price",
      "£" & Text(
         Sum(
           Filter(JobCollection, DateCalc=Text(WkEndingDate.SelectedDate-4,"[$-en-GB]yyyymmdd")),
           JobPrice),
         "#,###0.00")
      )
   )

It's worth noting that there's a bug in PowerApps. If you attempt to use a text format that includes the £ symbol, PowerApps displays the $ symbol instead. Therefore, this technique prevents this problem. There's more details about this in the post below. 

https://powerusers.microsoft.com/t5/PowerApps-Ideas/Provide-better-formatting-solution-for-Currency-...

View solution in original post

7 REPLIES 7
Highlighted
Super User III
Super User III

Re: Showing the result of a SUM from a column in a collection as a currency

@d3ell 

Your syntax is a little off on the text formatting.  In your formula, you are formatting the JobPrice within the sum.  I believe what you might want instead is this formula:

If(VarFormat="Hourly", 
   Sum(
      Filter(JobCollection,
             DateCalc=Text(WkEndingDate.SelectedDate-4,"[$-en-GB]yyyymmdd")),
      JobHours), 
   If(VarFormat="Price",
      Text(
         Sum(
           Filter(JobCollection, DateCalc=Text(WkEndingDate.SelectedDate-4,"[$-en-GB]yyyymmdd")),
           JobPrice),
         "[$-en-GB]£#,###0.00")
      )
   )

I hope that is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Highlighted
Helper V
Helper V

Re: Showing the result of a SUM from a column in a collection as a currency

Hi Randy,

 

I figured it might be something like that, however this is still not working.

 

I'm still getting the correct amount shown, but no formatting of the currency?

Highlighted
Super User III
Super User III

Re: Showing the result of a SUM from a column in a collection as a currency

@d3ell 

What kind of column is JobPrice?  Perhaps doing a Value(JobPrice) on that might resolve it if it is not purely numeric.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Highlighted
Super User III
Super User III

Re: Showing the result of a SUM from a column in a collection as a currency

Hi @d3ell 

I think the best solution is to just append a "£" symbol to the start of your value.

 

If(VarFormat="Hourly", 
   Sum(
      Filter(JobCollection,
             DateCalc=Text(WkEndingDate.SelectedDate-4,"[$-en-GB]yyyymmdd")),
      JobHours), 
   If(VarFormat="Price",
      "£" & Text(
         Sum(
           Filter(JobCollection, DateCalc=Text(WkEndingDate.SelectedDate-4,"[$-en-GB]yyyymmdd")),
           JobPrice),
         "#,###0.00")
      )
   )

It's worth noting that there's a bug in PowerApps. If you attempt to use a text format that includes the £ symbol, PowerApps displays the $ symbol instead. Therefore, this technique prevents this problem. There's more details about this in the post below. 

https://powerusers.microsoft.com/t5/PowerApps-Ideas/Provide-better-formatting-solution-for-Currency-...

View solution in original post

Highlighted
Community Support
Community Support

Re: Showing the result of a SUM from a column in a collection as a currency

Hi @d3ell ,

Could you please share a bit more about the JobPrice column in your data source? Is it a Currency type column?

 

Currently, the Currency data type is not supported within PowerApps. If you want to display the Currency value within your app directly, I afraid that there is no way to achieve your needs in PowerApps currently.

 

As an alternative solution, I think the Text function could achieve your needs. Based on the formula that you mentioned, I think there is something wrong with it, please consider modify your formula as below:

 

If(
   VarFormat="Hourly",
   Sum(Filter(JobCollection,DateCalc=Text(WkEndingDate.SelectedDate-4,"[$-en-GB]yyyymmdd")),JobHours),
VarFormat="Price",
Text( /* <-- Modify formula here */
Sum(Filter(JobCollection,DateCalc=Text(WkEndingDate.SelectedDate-4,"[$-en-GB]yyyymmdd")), JobPrice),
"[$-en-GB]£ #,##0.00"
) )

Or

If(
   VarFormat="Hourly",
   Sum(Filter(JobCollection,DateCalc=Text(WkEndingDate.SelectedDate-4,"[$-en-GB]yyyymmdd")),JobHours),
   VarFormat="Price",
   "£" & Text(      /* <-- Modify formula here */
        Sum(Filter(JobCollection,DateCalc=Text(WkEndingDate.SelectedDate-4,"[$-en-GB]yyyymmdd")), JobPrice),
        "[$-en-GB]#,##0.00"
   )
)

More details about the Text function, please check the following article:

Text function

 

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.
Highlighted
Helper V
Helper V

Re: Showing the result of a SUM from a column in a collection as a currency

Thanks fo your help.  Your code almost worked for me. 

 

Not sure why this is the case and through trial and error i got this to work, bit only if i added something earlier on in the code.  by inserting the red parts in at the beggining the £ sign then appeared.  Not sure if this can be explained??

 

its working now anyway! 🙂

 

If(VarFormat="Hourly"," "& Sum(
      Filter(JobCollection,
             DateCalc=Text(WkEndingDate.SelectedDate-4,"[$-en-GB]yyyymmdd")),
      JobHours), 
   If(VarFormat="Price",
      "£" & Text(
         Sum(
           Filter(JobCollection, DateCalc=Text(WkEndingDate.SelectedDate-4,"[$-en-GB]yyyymmdd")),
           JobPrice),
         "[$-en-GB]#,###0.00")
      )
   )

 

Highlighted
Super User III
Super User III

Re: Showing the result of a SUM from a column in a collection as a currency

Glad you got this working @d3ell 

Because sum returns a number, I'm guessing that PowerApps doesn't like it when some parts of a If statement return a number, and other parts text. By prefixing the first Sum with an empty string, I guess you're fixing this by implicitly converting the number to text. Anyhow, I'm pleased you got this work through trial and error 🙂

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors
Users online (5,930)