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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

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

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.