Forum Discussion
Helper IIIhow to format a number column
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!
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.
9 Replies
- mahoneypat
Microsoft Employee
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
sks2701Hello 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
