Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jimgores
Advocate I
Advocate I

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
Super User
Super User

@jimgores  try this in DAX

 

 

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

 

Capture.PNG

 

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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
Super User
Super User

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

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
PhilipTreacy
Super User
Super User

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!


That is a pretty slick solution too!  Thanks!

mahoneypat
Employee
Employee

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
Super User
Super User

@jimgores  try this in DAX

 

 

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

 

Capture.PNG

 

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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")

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

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.