Custom Format Strings in Power BI

The ability to apply custom format strings directly inside the Power BI Desktop report view was first announced back in February 2020. At the time I was quite excited and tweeted about it only to find out shortly thereafter that it hadn’t been released at all! It seems it was a release candidate that was pulled at the last minute, but no one updated the announcement! Then late last week I was doing some work in Power BI desktop and noticed that this feature has now been released and is working (I don’t recall seeing a new announcement at all).

Below I show you how to use custom formatting strings in Power BI.

How To Use Custom Format Strings in Power BI

It’s actually pretty easy. After you write a measure inside Power BI Desktop (after you press enter), you will be taken to the Measure Tools menu (#1) below. From there you will see the measure in the formula bar (#2 below) and from there you can format your measure (#3 below).

What has changed now is you no longer have to accept the various default options from the dropdown list.

You now have the ability to add custom format strings using a standard formatting approach similar to those used in other applications, such as Excel. If you want to get some inspiration, you can take a look at some of the custom formatting options provided inside Excel.

As you can see below, I have added the sales total from the Adventure Works database into cell J3 (shown as #1 below). From the home tab (#2) you can see the formatting dropdown list (#3).

From that dropdown list, if you select “More Number Formats”, then “Custom”, you will see a stack of examples that you can use for inspiration so you know what you can do inside Power BI too.

When you select one of the number formats from the list (e.g. #1 below), you can actually copy the format string shown in #2 below and use this string to help you author what you need for Power BI without the need to start from scratch (at least until you get the hang of it).

Two Things to Note

Font Colour

Firstly, any of the colour styling that you cut and paste from the format strings above will not work. You need to remove those. So if you copy this

#,##0;[Red]-#,##0

You need to edit it so it looks like below

#,##0;-#,##0

You need to use conditional formatting in Power BI to control the font colour.

Escaped Characters

There are some characters that are designed to do specific things in these text strings, for example.

• Digits 0 (representing a mandatory digit placement)
• # symbol (representing an optional digit placement)
• Thousand separator
• Decimal separator
• d which is used for days inside a date format
• m which is use for months inside a month format

Some other characters will work, some will not. I recommend if you actually want a keyboard character to appear in your format string, that you “escape” the character by using a backslash. Here is an example.

Suppose you want to add the characters INR in front of your numbers as shown below #1 (e.g., for Indian Rupees)

Actually, there are 2 ways of doing it.  You could write the format string as follows, with a backslash in front of every letter you want to appear before the number.

\I\N\R #,##0;\I\N\R -#,##0

Alternatively, you can use quotes, like this

“INR” #,##0;”INR” -#,##0

For single characters, the backslash is probably the easiest; for multiple characters the quotes are probably the easiest.

Who Knew?

Unless someone told you, it is possibly you would never know you could do this. This is particularly likely given that previously it wasn’t possible at all. So if you tried previously and it didn’t work, you probably wouldn’t try again.

Well, now you know.