Dynamic formatting for negative values

[bbass82]

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 · Accepted solution]

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]

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.