How to format a Number to Thousands or Millions using Format Function in DAX

How to format a Value to Thousands or Millions using Format Function in DAX
Let suppose we have Table with a List of Values as per below:

Numbers=

VAR _List = { 103, 10345, 103456, 1034567, 103456789, 134897891,

                103456789102, 103499789102, 1034567891023 }

VAR _tblNumbers = SELECTCOLUMNS( _List, "NumValue", [Value])

RETURN

_tblNumbers

Now we can covert the above Numbers to Thousands or Millions format with the help of below Measures:

SkipThousands =

    VAR CurrentNumber =

        SELECTEDVALUE ( Numbers[NumValue] )

    VAR LogRatio =

        IF ( CurrentNumber > 0, DIVIDE ( LOG ( CurrentNumber ), LOG ( 1000 ) ) )

    RETURN

        ROUNDDOWN ( DIVIDE ( LOG10 ( CurrentNumber ), 3 ), 0 )

FormatString=

    SWITCH (

        [SkipThousands],

        0, "$#,0",       -- Integer number

        1, "$#,0,.0#K",  -- Thousand

        2, "$#,0,,.0#M", -- Million

        3, "$#,0,,,.0#B", -- Billion

        "$#,0,,,,.0#T"   -- Trillion

        )

Formatted_Value = FORMAT(MAX(Numbers[NumValue]), [FormatString])

Result:

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------