Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Auto-Format Numbers in Billions, Millions, Thousands, etc.

Hi,

I came across the need to have charts auto-format numbers depending on their size. For example, if numbers are in billions, format in billions. Same thing for millions and thousands. Would like to prevent users from having to scroll to see all the columns, and this would help in that regard.

Thanks!

Josh

Status: New
Comments
JeffWeir
Advocate IV

Great idea. I've written DAX to do this dynamically. 

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

 

See this Twitter thread: https://twitter.com/InsightsMachine/status/1313428654647472128

 

Something similar can also be used inside Calculation Groups to keep the numbers as numbers, as per this thread: https://twitter.com/InsightsMachine/status/1313603548995751937?s=20

 

 

 

 

Stefanatic
Regular Visitor

How about "just" like that? 🤔

Dynamic Total = 
VAR Tot = Sum('Table'[Current Total])
RETURN IF(ABS(Tot)>1000000,
          FORMAT(Tot, "#,,.0M"),
           IF(ABS(Tot)>1000,
             FORMAT(Tot,"#,K"),
          FORMAT(Tot, "0")
             )
          )

 

splambo
Helper I

How would you apply this solution to a pie chart? I can't find a way to change PowerBI's default bn to B or anything else because all formulas create a text output, but the pie chart visual seems to only use a number output and apply its bn.

Stefanatic
Regular Visitor

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"),

 

splambo
Helper I

Thanks @Stefanatic ... poor pie charts get no love 😂 but I managed to compromise with a donut chart.

However the issue remains: Chart visuals only accept numerical values, but there is no fx formula field for anything except color in their Detail Label settings in the Format pane.  I haven't found a way to assign a measure to the detail labels. See screenshot.

dynamic number formatting for visuals detail labels.png

Stefanatic
Regular Visitor

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"
             )
          )
        )

Stefanatic_1-1683903994607.png

 

 

splambo
Helper I

Great suggestion! This feature is only available in PBI Desktop version 2.116.622.0 or later, as mentionned in another article:
 How to use the new dynamic format strings for measures in Power BI – Data Savvy

I hope to get this versionin order to try the solution. Cheers.

Stefanatic
Regular Visitor

No worries! Hope it worked in the meantime?

splambo
Helper I

I got an updated powerbi version and am using the dynamic format to format string measures and it simply seems buggy.

I'm trying to format millions to a shortened format such as $8.9M but the output in the chart's detail labels is inconsistent (and wonky!);

 

Data               Desired result          Actual result
8,999,274         $8.9M                     $9.0M
13,156,240       $13.2M                   $132M
20,271,133       $20.3M                   $2202711333M


My Measure formula is:

Measure = MAX(table[column])

 

My format formula is:

VAR Tot = MAX('table'[column])
RETURN FORMAT(Tot/1000000, "$#,##0.0M")

{65656550-DD3F-4A49-B42D-06C145D34D2A}.png

Stefanatic
Regular Visitor

Oh, that sure looks weird. I hope that do 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"
             )
          )
        )