Forum Discussion
how to format a number column
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.
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.
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.
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.