topic Re: SORT NEGATIVE NUMBERS IN ORDER in DAX Commands and Tips

SORT NEGATIVE NUMBERS IN ORDER

cuohanele — Mon, 10 Aug 2020 11:56:51 GMT

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!

Re: SORT NEGATIVE NUMBERS IN ORDER

Greg_Deckler — Mon, 10 Aug 2020 11:58:16 GMT

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.

Re: SORT NEGATIVE NUMBERS IN ORDER

amitchandak — Mon, 10 Aug 2020 12:48:40 GMT

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

Re: SORT NEGATIVE NUMBERS IN ORDER

Anonymous — Mon, 10 Aug 2020 16:09:02 GMT

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

You handle this through custom number formats.

Re: SORT NEGATIVE NUMBERS IN ORDER

cuohanele — Mon, 10 Aug 2020 19:26:05 GMT

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.

Re: SORT NEGATIVE NUMBERS IN ORDER

cuohanele — Mon, 10 Aug 2020 19:27:14 GMT

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