cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cuohanele
Helper I
Helper I

SORT NEGATIVE NUMBERS IN ORDER

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!
5 REPLIES 5
Anonymous
Not applicable

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

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. 

amitchandak
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

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

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

Greg_Deckler
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

Announcements
Exciting changes

Power BI Community Changes

Check out the changes to the Power BI Community announced at Build.

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Kudo Data Story carousel

Data Stories Gallery

Visit our Data Stories Gallery and give kudos to your favorite Data Stories.

Top Solution Authors