Forum Discussion
Custom Number Format in thousands with decimals
Hi,
When assigning a custom number format I thought it would work exactly like number formats in Excel. (I am from Belgium)
I have this number: 644.167.299,59 that I would like to format so takes less space (similar to a card visual).
The result I want is this: 644,17M
In Excel I can do that by using this custom number format: #.##0,00.."M" (or #,##0.00,,"M" depending on my period settings)
Unfortunately when I use this format in PBI it gives me this: 644167299,589,08 or 644.167.299,59,,M
So not what I want.
I did find in another post that you can do K/M/B by setting the grouping separator behind the number
=> #,##0"M",, gives me 644M
=> #,##0"K", gives me 644.167K
However once I now try to add the decimal it defaults back to the full string (with or without adding the "M" as an extra complicator.
=> #,##0.00,, gives 644.167.299,59,, instead of 644,17
I guess this is unintended behavior (I wouldn't know why they would like to NOT duplicate Excel behavior in this). Is there a solution?
Hi matthias_vc
Please try this dynamic format string below:
VAR _v = ABS ( [Amt] ) -- replace with your measure RETURN SWITCH ( TRUE(), _v = 0, "0", _v < 1, "#.###", -- e.g. 0.123 _v < 10, "0.0", -- e.g. 1.2 _v < 100, "0.##", -- e.g. 12.3 _v < 1000, "0.00", -- e.g. 123.45 _v < 1000000, "#,##0,.0K", -- thousands → 1.2K _v < 1000000000, "#,##0,,.0M", -- millions → 1.2M _v < 1000000000000, "#,##0,,,.0Bn", -- billions → 1.2Bn "#,###" -- fallback )For this to work on visuals other than a table or a matrix, ensure that the display units of X-axis is set to none
Please see the attached pbix.
3 Replies
Hi matthias_vc
Please try this dynamic format string below:
VAR _v = ABS ( [Amt] ) -- replace with your measure RETURN SWITCH ( TRUE(), _v = 0, "0", _v < 1, "#.###", -- e.g. 0.123 _v < 10, "0.0", -- e.g. 1.2 _v < 100, "0.##", -- e.g. 12.3 _v < 1000, "0.00", -- e.g. 123.45 _v < 1000000, "#,##0,.0K", -- thousands → 1.2K _v < 1000000000, "#,##0,,.0M", -- millions → 1.2M _v < 1000000000000, "#,##0,,,.0Bn", -- billions → 1.2Bn "#,###" -- fallback )For this to work on visuals other than a table or a matrix, ensure that the display units of X-axis is set to none
Please see the attached pbix.
Thanks! the Format strings worked.
I didn't use your dynamic format string because I don't want it to be everywhere, but I did use it in a calculation group.You should still be able to overwrite the format string from the format options within each visual.
