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

Stunts schmunts.  Try this:

 

 

 

Sales$ :=
IF (
    ISCROSSFILTERED ( 'Parameter'[calculations] ),
    SWITCH (
        TRUE (),
        VALUES ( 'Parameter'[calculations] ) = "YoY%", IF (
            ABS ( [Sales$ YoY%] ) < 1,
            RIGHT ( "  " & FORMAT ( [Sales$ YoY%], "0.0%" ), 5 ),
            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 ()
)

 

 

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

You are on a roll! So the currency values have been FIXED and the percentages look good with only a very minor issue. See picture below:

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

    Add another space and change the 5 to 6.

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

      That works! Thanks a bunch. This is the final DAX:

       

      Sales$ (Table) :=
      IF (
      ISCROSSFILTERED ( 'Parameter'[calculations] ),
      SWITCH (
      TRUE (),
      VALUES ( 'Parameter'[calculations] ) = "YoY%", IF (
      ABS ( [Sales$ YoY%] ) < 1,
      RIGHT ( " " & FORMAT ( [Sales$ YoY%], "0.0%" ), 6 ),
      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 ()

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

        If there were negative percentages, how will the code look? Ran into that issue

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

      What if there are negative numbers? I tried applying  the same logic of your previous DAX to other columns with negative percentages and negative dollar amounts. I ran into this:

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago
        Hi

        Didn't I say already that you'll be having a pretty hard time to get this to work correctly? And there'll probably always be cases not accounted for... If I were you, I'd stay away from such BRITTLE CODE.