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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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

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:

 

Capture2.PNG

 

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

 

Capture1.PNG

 

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?  

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.  

View solution in original post

5 REPLIES 5
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
az38
Community Champion
Community Champion

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


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Anonymous
Not applicable

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?

 

Capture3.PNG

az38
Community Champion
Community Champion

@Anonymous 

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

Безымянный.png

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


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Anonymous
Not applicable

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.  

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Top Solution Authors