Forum Discussion

bbass82's avatar
bbass82
Helper I
1 year ago
Solved

Dynamic formatting for negative values

Hello Community friends,

 

Was hoping someone could help me with this issue I am having in regards to dynamic formatting of my values in my DAX. I have some negative values that I want to show in the same format (no negative values in other columns). 

 

I wrote the below DAX in hopes of returning values with the below formatting:

1) Show all negative #'s equal to or greater than -$1,000,000 as formatting of "-$1.0M"

2) Show all negative #'s between -$999,999k to -$0k as formatting of "-$999.9k"

Current State:

 

Any help with this is greatly appreciated. 

 

Kind Regards,

Brandon Bassett

  • OwenAuger's avatar
    OwenAuger
    1 year ago

    Hi bbass82 

    I think you just need to add one condition within SWITCH to your current expression to handle negative numbers between -1m and zero.

    I would write the overall expression like this with SELECTEDMEASURE assigned to a variable:

     

     

    VAR MeasureValue = SELECTEDMEASURE ( )
VAR Result =
    SWITCH (
        TRUE ( ),
        MeasureValue < -1e6, "$#,##0,,.0M",
        MeasureValue < 0, "$#,##0,.0k", -- new condition
        MeasureValue < 1e3, "$#,##0",
        MeasureValue < 1e6, "$#,##0,.0k",
        -- Otherwise >= 1e6
        "$#,##0,,.0M"
    )
RETURN
    Result

     

     

    PBIX attached in case that helps debug.

    You could consider using conditions based on ABS ( SELECTEDMEASURE () ) if the same format string applies to positive/negative values in the same absolute value range.

     

    Are you able to get it working at your end?

     

    Regards

    Owen

2 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    1 year ago

    Hi bbass82 

    I think you just need to add one condition within SWITCH to your current expression to handle negative numbers between -1m and zero.

    I would write the overall expression like this with SELECTEDMEASURE assigned to a variable:

     

     

    VAR MeasureValue = SELECTEDMEASURE ( )
VAR Result =
    SWITCH (
        TRUE ( ),
        MeasureValue < -1e6, "$#,##0,,.0M",
        MeasureValue < 0, "$#,##0,.0k", -- new condition
        MeasureValue < 1e3, "$#,##0",
        MeasureValue < 1e6, "$#,##0,.0k",
        -- Otherwise >= 1e6
        "$#,##0,,.0M"
    )
RETURN
    Result

     

     

    PBIX attached in case that helps debug.

    You could consider using conditions based on ABS ( SELECTEDMEASURE () ) if the same format string applies to positive/negative values in the same absolute value range.

     

    Are you able to get it working at your end?

     

    Regards

    Owen

    • bbass82's avatar
      bbass82
      Helper I
      1 year ago

      Hi Owen,

       

      Thank-you so much for this solution. Worked perfectly for what I was trying to show in my pbix. I am still new to the absolute conditions piece and will be studying up as suggested on how to you utilizate absolutes within my dax.