SORT NEGATIVE NUMBERS IN ORDER

[cuohanele]

Hello!

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:

-29

-14

-13

-11

-6

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]),

SWITCH( TRUE(),

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

),

Thanks!

[Anonymous]

This is how you do such things properly:
https://youtu.be/72qQrPw4RuE?t=304

You handle this through custom number formats.

[cuohanele]

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. 

[amitchandak]

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

in power query

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

sort the original column on new column

https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column

[cuohanele]

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

[Greg_Deckler]

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