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:

Notes: We can also write the Scale conversion logic as shown in below example:

Sales Amount Ratio =

VAR _Sales = [Total Sales Amount]

VAR _Retail_Sales_Pct = FORMAT([% Retail Sales Amount], "0.00%")

VAR _Scaled_Value =

    SWITCH(

        TRUE(),

        ABS(_Sales) >= 1000000000, FORMAT(_Sales / 1000000000, "0.00") & " Bn",

        ABS(_Sales) >= 1000000,    FORMAT(_Sales / 1000000, "0.00") & " M",

        ABS(_Sales) >= 1000,       FORMAT(_Sales / 1000, "0.00") & " K",

        FORMAT(_Sales, "0.00")

    )

RETURN

    IF(NOT ISBLANK(_Sales), _Scaled_Value & " | " & _Retail_Sales_Pct)

Sample Result: 6.74 M | 52.76 %

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

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

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