Forum Discussion

cuohanele's avatar
cuohanele
Helper I
6 years ago
Solved

DYNAMIC SORTING

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!

  • 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!

     

     

2 Replies

  • edhans's avatar
    edhans
    Community Champion

    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.)

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support

    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!