User Profile
Stefanatic
Regular Visitor
Joined 4 years ago
User Widgets
Contributions
Re: Auto-Format Numbers in Billions, Millions, Thousands, etc.
Oh, that sure looks weird. I hope that you want to see 9.0M for 8,999,274 (otherwise, you'd have to change the measure formula to include ROUNDUP). The format formula, however, needs a string as a result. What if you use following formula for the format (using the , as thousands separator)? var _max=MAX('Table'[Value]) RETURN IF(ABS(_max)>1000000000, "$#,#,,,.0B", IF(ABS(_max)>1000000, "$#,,.0M", IF(ABS(_max)>1000, "$#,.0MK", "$0" ) ) )Re: Auto-Format Numbers in Billions, Millions, Thousands, etc.
Ah sorry. Here's a way that should work: Under File-Options, you can turn on the Preview feature "Dynamic format string for measures". After a restart of Power BI, you can apply dynamic formatting as follows. 1. create a Measure 2. under 'Measure tools', select "Dynamic" Format 3. select "Format" instead of 'Measure' in the drop-down left 4. add the DAX formula equivalent to below, i.e. only the format string of the FORMAT function. 5. add the measure to your visualization. See below screenshot how it looks here. 👍🏼 --> Check this link for more examples. var tot=SUM('Table'[Value]) RETURN IF(ABS(Tot)>1000000000, "#,#,,,.0B", IF(ABS(Tot)>1000000, "#,#,,.0M", IF(ABS(Tot)>1000, "#,#,.0MK", "#,#.0" ) ) )Re: Auto-Format Numbers in Billions, Millions, Thousands, etc.
If you (really 😉) want to use a pie chart, you can just use above measure. It returns a number output with dynamic formatting. Of course, if the figures go into billions, you can adapt the formula above to include following. IF(ABS(Tot)>1000000000, FORMAT(Tot, "#,,,.0B"),
