how to format a number column

[sks2701]

Hi- I have a revenue column in my data set - I would like to format this in the short format in the table view so for example if the cokumn contains 2140100000 as value in the table i want to format it like $2.1B and if the value is 698000000 i want it to format like $698M and so on - please can someone help the steps- thanks!

[mahoneypat]

Ideally, you would use Custom Format Strings to do this, but it does not appear to work the same as Excel when adding a condition.  For example, this custom format string works in Excel but not in Power BI Desktop.

[<100000000]#,,.0"M";#,,,.0"B"

However, you can write a DAX expression with an IF and the FORMAT function to get the same result.  Don't add this as a column to your table (since it returns text), but use it in a Measure once all your calculation is done to return the result in the desired format.

NumberFormat =
VAR thisnumber =
    MIN ( CustomNumber[Formatted] )
RETURN
    IF (
        thisnumber < 1000000000,
        FORMAT ( thisnumber, "###,,.0M" ),
        FORMAT ( thisnumber, "###,,,.0B" )
    )

Pat

[sks2701]

Hello @mahoneypat - thank you so much for your time, however this is still not working for me . attaching a screen shot for your reference. please can you look into it.

[mahoneypat]

Remove "NumberFormat =" (that is what I called the measure but you already have a name for it.  Also replace what is in the MIN(  ) with your actual TableName[ColumnName].

Pat

[sks2701]

no luck 

[Icey · Accepted solution]

Hi @sks2701 ,

If "[Total Funding Amount Currency (in USD)]" is a measure, it is unnecessary to use MIN() function. Just try this:

Measure =
VAR thisnumber = [Total Funding Amount Currency (in USD)]
RETURN
    IF (
        thisnumber < 1000000000,
        FORMAT ( thisnumber, "###,,.0M" ),
        FORMAT ( thisnumber, "###,,,.0B" )
    )

Or this:

Measure =
VAR SafeLog =
    IFERROR (
        ABS ( INT ( LOG ( ABS ( [Total Funding Amount Currency (in USD)] ), 1000 ) ) ),
        0
    )
VAR dp = 1
RETURN
    ROUND (
        DIVIDE ( [Total Funding Amount Currency (in USD)], 1000 ^ SafeLog ),
        dp
    )
        & SWITCH ( Safelog, 1, "K", 2, "M", 3, "bn", 4, "tn" )

If "[Total Funding Amount Currency (in USD)]" is a column, it is necessary to use function like SUM(), MIN(), etc.. Try this:

Measure =
VAR thisnumber =
    SUM ( [Total Funding Amount Currency (in USD)] )
RETURN
    IF (
        thisnumber < 1000000000,
        FORMAT ( thisnumber, "###,,.0M" ),
        FORMAT ( thisnumber, "###,,,.0B" )
    )

Or this:

Measure =
VAR SafeLog =
    IFERROR (
        ABS (
            INT ( LOG ( ABS ( SUM ( [Total Funding Amount Currency (in USD)] ) ), 1000 ) )
        ),
        0
    )
VAR dp = 1
RETURN
    ROUND (
        DIVIDE ( SUM ( [Total Funding Amount Currency (in USD)] ), 1000 ^ SafeLog ),
        dp
    )
        & SWITCH ( Safelog, 1, "K", 2, "M", 3, "bn", 4, "tn" )

Reference: Auto-Format Numbers in Billions, Millions, Thousan... - Microsoft Power BI Community

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sks2701]

@Icey thanks a ton - the below formula  worked as Total Funding Amount Currency (in USD) is a column

Measure = VAR thisnumber = SUM ( [Total Funding Amount Currency (in USD)] ) RETURN IF ( thisnumber < 1000000000, FORMAT ( thisnumber, "###,,.0M" ), FORMAT ( thisnumber, "###,,,.0B" ) )

However in the table visual , how can i arrange this measure column from highest to lowest value so currently with the present view the B is not appearing in the top row- please check the below screen shot.

[Icey]

Hi @sks2701 ,

You can put the "[Total Funding Amount Currency (in USD)]" column into your Table visual and then close the option of "Word Wrap" of "Column header" and "Value" in the visual formatting field. Then, you can hide the column.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat]

You are missing the  ( ) in your MIN function.  MIN(Table[Total ...])

Pat

[watkinnc]

I bet Column From Examples would tackle that with ease...