cancel
Showing results for
Did you mean:

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

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.
Helper I

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

@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

Helper I

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

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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors