Forum Discussion
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,
KellyDid I answer your question? Mark my post as a solution!
2 Replies
- edhansCommunity 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-msftCommunity 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,
KellyDid I answer your question? Mark my post as a solution!