Unable to formated calculated measure as number - text shows as only option

[Anonymous]

I'm trying to sort a chart by a calculated measure, but it sorts incorrectly.  I believe it's because I'm trying to sort numbers that are stored as text, but it will not allow me to change the data type to a number.  

See chart that's sorted incorrectly:

See calculated measure and the inability to change the data type to number.

When I click on the text at the top, everything is greyed out except the text option.  

I've searched around but have not found any solution that works....can someone please help?  

[v-shex-msft]

HI @Anonymous,

I'd like to suggest you enter to query editor to remove all specific characters that existed in your numeric fields, then you can format these fields as normal number types.

Replace values (Power Query) 

Regards,

Xiaoxin Sheng

[az38]

Hi @Anonymous 

of course, as your column contains parenthesyses, it can not be a number - only text

you have to either change format to number look-like or use Sort by column function with column [fees_adjusted] as sort parameter https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column 

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

Thanks @az38 , but I'd still like to show negative numbers with  with parens (###).  I created "Total Adjust 2" as a column as opposed to measure.  I changed "Total Adjust" back to the -# format, so I could sort it correctly.  Is there a way I can hide "Total Adjust" in order to preserve the correct sort and just show "Total Adjust2"?  If not, can you elaborate more on the sort parameter you mentioned may help?

[az38]

@Anonymous 

if you do smth like this you will be able to hide "Total Adjust" from your visual

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous · Accepted solution]

Thanks - I just created a new measure without formatting the numbers, sorted it, and hid the column by minimizing width.  This seemed to be the best workaround.