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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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.


Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.