cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jimgores
Frequent Visitor

Convert currency to text, but keep currency symbol and formatting

Hello,

I'm trying to figure out how to convert a column of currency values to text while also keeping the $ symbol and thousands separator. I've been trying a number of things in Power BI, but nothing has worked.  Is there a way to do this using DAX or M?  I wouldn't be surprised if I'm missing something obvious, but my Google searches haven't turned up anything either.

 

Thanks!

2 ACCEPTED SOLUTIONS
smpa01
Resident Rockstar
Resident Rockstar

@jimgores  try this in DAX

 

 

Column = FORMAT([Column1],"$#,##0.00")

 

Capture.PNG

 

 

 


New Animated Dashboard: Sales Calendar


View solution in original post

Hi @jimgores 

Download this example PBIX file with the following data.

You can do this with Custom Format Strings without needing to actually convert the currency to text and create another column.

If your data looks like this

curr-vals.png

You can apply this format string

 

 

"$"#,##0.00;-"$"#,##0.00;"Not Reported"

 

 

It's like in Excel where you have separate components for the formatting string, in this case the string takes the structure Positive Format;Negative Format;Zero Format so 0 values will be displayed as Not Reported

Just type the format string into the Format area and hit Enter

not-rep.png

Further reading if you are interested

https://www.myonlinetraininghub.com/custom-formatting-strings-in-power-bi

Regards

Phil

 



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

11 REPLIES 11
smpa01
Resident Rockstar
Resident Rockstar

@jimgoresin my screenshot Column1 is the decimal number formatted as currency. What is your column1? Can you give me the an example? Is it a string or decimal number or whole number? Please provide an example.


New Animated Dashboard: Sales Calendar


PhilipTreacy
Super User III
Super User III

Hi @jimgores 

Why do you want to convert numbers to text and lose the ability to (easily) analyse the values?

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


My collegue needs the value $0 to return "Not Reported" instead of $0 when it occurs.  The only way I can see to accomplish this is to convert the column to text and then replace values.

Hi @jimgores 

Download this example PBIX file with the following data.

You can do this with Custom Format Strings without needing to actually convert the currency to text and create another column.

If your data looks like this

curr-vals.png

You can apply this format string

 

 

"$"#,##0.00;-"$"#,##0.00;"Not Reported"

 

 

It's like in Excel where you have separate components for the formatting string, in this case the string takes the structure Positive Format;Negative Format;Zero Format so 0 values will be displayed as Not Reported

Just type the format string into the Format area and hit Enter

not-rep.png

Further reading if you are interested

https://www.myonlinetraininghub.com/custom-formatting-strings-in-power-bi

Regards

Phil

 



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

That is a pretty slick solution too!  Thanks!

mahoneypat
Super User IV
Super User IV

You can use custom format strings (see link).  That way you can get the currency symbol and still do math on that column/use it in visuals/etc.

Use custom format strings in Power BI Desktop - Power BI | Microsoft Docs

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


I've seen this, but from the way I read it, you still need to keep the values formatted as a number.  I need to convert the numbers to text.

smpa01
Resident Rockstar
Resident Rockstar

@jimgores  try this in DAX

 

 

Column = FORMAT([Column1],"$#,##0.00")

 

Capture.PNG

 

 

 


New Animated Dashboard: Sales Calendar


View solution in original post

jimgores
Frequent Visitor

I was very hopeful when I tried this, but I get the error, "Cannot convert value '' of type Text to type Number."

Thank you smpa01, you got me pointed in the right direction.  I had to convert the column to a string 

Column1 = CONVERT([Column],STRING) and then use your formula Column2 = FORMAT([Column1],"$#,##0.00")

smpa01
Resident Rockstar
Resident Rockstar

@jimgores  can you please provide sample data and tell me what is the data type of column1 value in your example ?


New Animated Dashboard: Sales Calendar


Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors