DYNAMIC SORTING

[cuohanele]

Hi everyone! In need of some assistance.

I created a dynamic format for a column in my table to be filtered by a specific slicer. Unfortunately, it is sorting the numbers like they are string. I used the code below:

Sales$ = IF(ISCROSSFILTERED('Parameter'[calculations]),
SWITCH( TRUE(),
    VALUES('Parameter'[calculations]) = "YoY%",
SWITCH( TRUE(),
ABS([Sales$ YoY%]) < 1, FORMAT([Sales$ YoY%],"0.0%"),
ABS([Sales$ YoY%]) >= 1, FORMAT([Sales$ YoY%],"0%"),
BLANK()
),
    VALUES('Parameter'[calculations]) = "YoY Gap", CONCATENATE("$", FORMAT([Sales$ YoY Gap],"0,0")),
    VALUES('Parameter'[calculations]) = "Current Year Actuals", CONCATENATE("$", FORMAT([Sales$ CY],"0,0")),
BLANK()), BLANK ())

When any of the options in the slicer is picked, it returns this column sorted out of order. See below for visual:

So it is sorting something like this:
100
1
2
200
222
3
33

Thanks in advance!

[v-kelly-msft · Accepted solution]

Hi @cuohanele ，

Using "format" function is to change the number into text format,so go to "column tools " in the menu bar to set the number into "percent" format.

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[edhans]

They are strings. The FORMAT() function  converts values to text. You'd need to have leading zeros to format correctly. I am not aware of a way to conditionally format numbers by number format (currency, percentage, etc.)