Forum Discussion

cuohanele's avatar
cuohanele
Icon for Helper I rankHelper I
6 years ago
Solved

DYNAMIC FORMAT USING DAX FOR SLICER

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 use...
  • cuohanele's avatar
    cuohanele
    6 years ago

    GOT IT!!!

     

    I took the initial code you sent that fixed the percentage values and switched it out with the new line of code but left the currency values as is. THIS IS THE CODE THAT WORKS. Thank you so much!!!

     

     

    Sales$ (Table) =
    IF (
    ISCROSSFILTERED ( 'Parameter'[calculations] ),
    SWITCH (
    TRUE (),
    VALUES('Parameter'[calculations]) = "YoY%",
    SWITCH( TRUE(),
    ABS([Sales$ YoY%]) < 0.1, FORMAT([Sales$ YoY%]," 0.0%"),
    ABS([Sales$ YoY%]) < 1, FORMAT([Sales$ YoY%]," 0.0%"),
    FORMAT([Sales$ YoY%],"0%")
    ),

    VALUES ( 'Parameter'[calculations] ) = "YoY Gap", RIGHT ( " $" & FORMAT ( [Sales$ YoY Gap], "0,0" ), 12 ),
    VALUES ( 'Parameter'[calculations] ) = "Current Year Actuals", RIGHT ( " $" & FORMAT ( [Sales$ CY], "0,0" ), 12 ),
    BLANK ()
    ),
    BLANK ()
    )
  • lbendlin's avatar
    lbendlin
    6 years ago

    Add another space and change the 5 to 6.

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
6 years ago

cuohanele No, it is not the same. There are subtle differences.  Try it out.

cuohanele's avatar
cuohanele
Icon for Helper I rankHelper I
6 years ago

Wow! It definitely made a change but we still ran into an issue. It's sorting the 100's like they are the number 10. See below:

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    6 years ago

    try this

     

    Sales$ := IF(ISCROSSFILTERED('Parameter'[calculations]),
SWITCH( TRUE(),
    VALUES('Parameter'[calculations]) = "YoY%",
SWITCH( TRUE(),
ABS([Sales$ YoY%]) < 0.1, FORMAT([Sales$ YoY%],"  0.0%"),
ABS([Sales$ YoY%]) < 1, FORMAT([Sales$ YoY%]," 0.0%"),
FORMAT([Sales$ YoY%],"0%")
),
    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 ())
    • cuohanele's avatar
      cuohanele
      Icon for Helper I rankHelper I
      6 years ago

      We're getting somewhere! So it sorted the percentages right! But now the currency values are out of order (YOY GAP and Current Year Actuals). See below: