Convert currency to text, but keep currency symbol and formatting

[jimgores]

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!

[smpa01 · Accepted solution]

@jimgores  try this in DAX

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

[smpa01]

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

[PhilipTreacy]

Hi @jimgores 

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

Regards

Phil

[jimgores]

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.

[PhilipTreacy · Accepted solution]

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

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

Further reading if you are interested

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

Regards

Phil

[jimgores]

That is a pretty slick solution too!  Thanks!

[mahoneypat]

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

[jimgores]

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 · Accepted solution]

@jimgores  try this in DAX

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

[jimgores]

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

[jimgores]

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]

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