Showing results for 
Search instead for 
Did you mean: 
Helper I
Helper I




I'm currently working on a table in desktop that needs the negative numbers to be sorted properly. I formatted the numbers to have units behind them so the column looks something like this:

-11 bps

-13 bps

-14 bps

-29 bps

-6 bps


I want it to look like this:







I realize that PBI is seeing these numbers as text. How can I fix this? I was able to use this DAX to sort all the other numbers without negatives and it worked but the negative values are giving me issues.


GM% = IF(ISCROSSFILTERED('Parameter'[calculations]),
     VALUES ( 'Parameter'[calculations] ) = "YoY%", SWITCH( TRUE(),
       ([GM% bps]) < 0, FORMAT ( [GM% bps], " 0 bps"),
      ([GM% bps]) < 10, FORMAT ( [GM% bps], " 0 bps"),
      ([GM% bps]) < 100, FORMAT ( [GM% bps], " 0 bps"),
       FORMAT ( [GM% bps], "0 bps")
Not applicable

This is how you do such things properly:

You handle this through custom number formats.

Unfortunately the video does not pertain to my dilemma. I attempted but nothing worked


My requirements are to CONCAT "bps" to the numbers so they come out like:


-1  bps

-11 bps

-2 bps

-222 bps

-3 bps


Because of the addition of the "bps" it seems PBI does not know how to sort the numbers in order. 

Super User
Super User

@cuohanele , create a number column by removing " bps"


in power query

New col =Text.Remove([Column]," bps")


sort the original column on new column

Cant. The values were created using a calculated measure so in power query, you do not see that column to manipulate

Super User
Super User

@cuohanele - If this is a column you could use a Sort By column and just make sure it is numeric and just the number portion.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors