Power BI Custom Format Strings - Making Numbers Read the Way People Expect

A report can have every number correct and still look wrong. I have seen it plenty of times. A finance dashboard where the revenue figure is technically accurate but sits there as "1240000.5" with no thousands separators, no dollar sign and a trailing decimal that means nothing to anyone. The CFO glances at it, frowns, and quietly loses a bit of trust in the whole thing. Not because the maths is off, but because it does not look like money. That gap between "the value is right" and "the value looks right" is where custom format strings do their work.

Format strings are the little code you attach to a field or measure that tells Power BI how to render the raw value. Currency, percentages, dates, phone numbers, custom units, colours for negatives, all of it. Microsoft's documentation on custom format strings covers the full syntax. I want to walk through the parts that matter in real client reports, because getting this right is one of the cheapest ways to make a dashboard look like a professional built it.

Why the built-in formats are not enough

Power BI gives you a dropdown of standard formats: currency, percentage, whole number, a handful of date styles. For a lot of reports that is fine. You pick "Currency" and you are done. The trouble starts when the client wants something the dropdown does not offer, and that happens more often than you would think.

They want revenue shown in thousands with a "K" suffix so the board page is not a wall of digits. They want negative variances in red and wrapped in brackets, accountant style. They want a date shown as "Mon 02 Sep" and nothing else. They want a percentage with exactly one decimal place, no more, no less, because two decimals looks fussy and zero looks imprecise. None of that is in the dropdown. All of it is a custom format string.

The syntax, in the parts you will actually use

A custom format string is a small pattern language. You do not need to memorise the whole thing, but a handful of characters cover most of what you will do.

The hash # is a digit placeholder that shows nothing if there is no digit there. The zero 0 is a digit placeholder that shows a zero if there is nothing. So #,##0 gives you "1,240" and "0", while #,### would give you "1,240" and blank for zero. That difference matters. A blank where a zero should be looks like a bug.

The comma does two jobs. Between digits it is a thousands separator. At the end, after the digits, it scales the number down by a thousand for each comma. So #,##0, divides by a thousand and #,##0,, divides by a million. This is how you get "1,240" to display as "1" thousand or a seven-figure number to collapse to a couple of digits.

The full stop is the decimal point. 0.0 gives one decimal, 0.00 gives two. Percentages use %, which also multiplies the value by 100, so a stored value of 0.142 with format 0.0% shows as "14.2%".

Text you want to appear literally, like a "K" or "M" suffix or a currency code, you wrap in double quotes or escape with a backslash. So a format like $#,##0.0,, "M" turns 2,400,000 into "$2.4 M". Getting the escaping right is fiddly and it is the single most common thing people get subtly wrong.

The four-section trick most people miss

Here is the bit that separates a tidy report from a polished one. A custom format string can have up to four sections separated by semicolons, and each section handles a different case: positive, negative, zero, and blank.

$#,##0.00;($#,##0.00);"-";"n/a"

That format shows positive numbers as "$1,240.00", negatives as "($1,240.00)" in brackets the way accountants read them, an actual zero as a clean dash "-" instead of "$0.00", and blank values as "n/a". One format string, four behaviours. The first time you show a finance team a variance report where the negatives are in red brackets and the zeros are dashes, they treat you like a wizard, when really you just used the semicolons.

You can add colour too. Put [Red] at the start of the negative section and your losses turn red automatically:

$#,##0;[Red]($#,##0)

No conditional formatting rules, no extra measures, just the format string doing the work. For a quick variance or P&L page this is faster and cleaner than setting up field-level conditional formatting.

Dates, which have their own vocabulary

Date formatting uses a different set of tokens. Lowercase d, dd, ddd, dddd give you the day as a number, padded number, short name and full name. The same pattern applies to months with m and years with y. So "ddd dd mmm yyyy" produces "Tue 02 Sep 2026".

The one that trips people up is the month versus minute clash. In date contexts mm means month, but in time contexts it means minutes. If you write a format for a datetime and your months are coming out as minutes, that is why. Put the mm next to hh and Power BI reads it as minutes; put it next to dd and it reads it as a month. Fiddly, and worth testing rather than assuming.

For Australian reports this matters more than people admit. Default US date formats sneak in through templates and sample files, and you end up with month-first dates in a report going to an Australian audience who read day-first. A stakeholder seeing "09/02/2026" cannot tell if that is September or the 9th of February. Set an explicit "dd/mm/yyyy" format and remove the ambiguity.

Where it goes wrong

I have cleaned up enough of these to know the failure modes.

The escaping is the big one. Currency symbols, backslashes and quotes all have meaning inside a format string, so you spend a bit of time getting the literal characters to appear literally. It is easy to write something that looks right in the editor and renders slightly off in the report, missing a symbol or showing a stray character. Build the format up one piece at a time and check it against a real value at each step rather than writing the whole thing and hoping.

The second trap is the scaling comma. Someone applies #,##0,, to show millions, then forgets, and later cannot work out why the numbers look a thousand times too small. The scaling is invisible in the format dropdown, so it catches people out weeks later. If a number looks wrong by a factor of a thousand, check the trailing commas first.

The third is over-formatting. Because you can do brackets and colours and suffixes and custom text, it is tempting to do all of it at once, and the report ends up busier than it needs to be. A dashboard where every number has a colour and a symbol and a suffix is harder to read, not easier. Format to remove ambiguity and to match how the audience already reads numbers, not to show off what the syntax can do.

The fourth is consistency across the report. Nothing looks more amateur than one page showing "$1,240K" and another showing "$1,240,000" for the same measure. If you are going to scale and suffix, do it everywhere that measure appears, or set the format on the measure itself so it travels with the number. This is the kind of thing that is easy to let slide under deadline and then embarrassing when the client notices.

Where this fits in the bigger picture

Format strings are a small thing. Nobody commissions a report because the negatives are in brackets. But the accumulation of small things is exactly what makes a report feel trustworthy, and a report the business does not trust is a report nobody uses. I have watched genuinely good data models get ignored because the front end looked rough, and I have watched fairly ordinary models get adopted because they looked considered. Presentation is not decoration. It is part of whether the work gets used at all.

This is the sort of detail the report work our Power BI consultants sweat over, because it is the difference between a dashboard that gets opened once and one that becomes part of how a team runs. The DAX and the modelling get the numbers right. The formatting is what makes people believe them. It also connects to the wider Microsoft Fabric and data work we do, where the reporting layer is usually the first thing a business actually sees and judges the whole platform by.

If you have got Power BI reports where the numbers are correct but they look unfinished, or you want reporting built properly from the model up, that is squarely our patch. Have a look at our services or get in touch and we will take a look at what you have got.