topic Re: Dynamic formatting for negative values in DAX Commands and Tips

Dynamic formatting for negative values

bbass82 — Tue, 04 Feb 2025 19:42:58 GMT

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

Re: Dynamic formatting for negative values

OwenAuger — Tue, 04 Feb 2025 22:01:18 GMT

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

Re: Dynamic formatting for negative values

bbass82 — Fri, 07 Feb 2025 15:52:04 GMT

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.