Power BI UDF Story of Number Formatting with Three Significant Figures

Lately my client asked me to create reports with “format in three significant numbers.” I didn’t know what the heck that was. I looked around, figured it out, and built it in Power BI. Here’s my story—and why I’m sharing it with you.

Turned out they meant: show big numbers in a readable way—like 123K instead of 123,131.1231, or 1.23M instead of 1,234,567. Same idea you see in the news (“1.2 million”) or in finance (“$1.2M deal”). Enough digits to compare and decide, not so many that your eye gets stuck. That’s “three significant figures” in practice.

The catch

If I did it the obvious way—one measure for Sales, one for Profit, one for Units, each with its own formatting and rounding—I’d be maintaining a pile of measures. So I put the logic in one place: a User-Defined Function (UDF) in the Power BI model. Write it once, use it everywhere. FormatKMB( [Total Sales] ), FormatKMB( [Profit] ), FormatKMB( [Units] ). That’s the use case that made UDFs click for me.

What you get

Before	After (FormatKMB)
12,345,678	12.3M
1,234,567	1.23M
123,456	123K
12,345	12.3K
1,234	1.23K
999	999

In a nutshell

flowchart LR A["Number e.g. 1,234,567"] --> B{Blank or 0?} B -->|Yes| C[Show nothing] B -->|No| D["Scale to 3 sig figs"] D --> E{Size?} E -->|>= 1M| F["1.23M"] E -->|>= 1K| G["1.23K"] E -->|small| H["999"] F --> I[Display] G --> I H --> I

Why three digits? Enough to see the difference between 1.2M and 1.3M—without pretending we know it down to the last cent. One rule for the whole report.

Why I’m sharing

So you don’t have to hunt for “three significant numbers” like I did. Add FormatKMB as a UDF in your semantic model (DAX below), then call it from any measure or card. One definition, same clean numbers everywhere. That’s the important use case: write once, use everywhere.

FormatKMB = (v: numeric) =>
  VAR abs_v = ABS(v)
  RETURN IF( ISBLANK(v) || abs_v = 0, BLANK(),
    VAR SafeAbs = MAX(abs_v, 1e-15)
    VAR Exponent = FLOOR(LOG10(SafeAbs), 1)
    VAR Scale = POWER(10, Exponent)
    VAR Mantissa = v / Scale
    VAR RoundedM = ROUND(Mantissa, 2)
    VAR ScaledBack = RoundedM * Scale
    VAR abs_s = ABS(ScaledBack)
    RETURN SWITCH( TRUE(),
      abs_s >= 1000000, SWITCH( TRUE(), abs_s >= 10000000, FORMAT(ScaledBack/1e6,"0.0")&"M", FORMAT(ScaledBack/1e6,"0.00")&"M" ),
      abs_s >= 1000,    SWITCH( TRUE(), abs_s >= 100000, FORMAT(ScaledBack/1e3,"0")&"K", abs_s >= 10000, FORMAT(ScaledBack/1e3,"0.0")&"K", FORMAT(ScaledBack/1e3,"0.00")&"K" ),
      FORMAT(ScaledBack, "0.##") ) )

FormatKMB — Power BI semantic model. One page. Numbers that read like 123K, not 123,131.1231.