Power BI Custom Format String Examples, Part 1: Numbers

Now that we can apply custom format strings to fields and measures in Power BI in the September 2019 release, I thought it would be useful to provide some examples of what’s possible with this very flexible new feature because the existing documentation for VBA isn’t easy to make sense of. In fact there’s so much to say I’m going to have to write a series of blog posts to cover everything! In this first post I’m going to look at formatting numbers.

First of all, here’s the source data I’m going to use for my examples:

image

I’m going to create a whole series of identical measures defined like this:

SalesEg1 = SUM('ExampleTable'[Sales])

…and apply different custom format strings to each one so you can compare the output in a Power BI table visual. For reference, here’s what a blank custom format gives you with this measure:

image

image

Let’s start with the basics of formatting numeric values. The first thing to point out is that custom format strings are built up using a series of placeholder characters that allow you to control things like thousands separators, the number of decimal places, whether digits are displayed in a placeholder and so on.

Setting the number of decimal places

As you can see in the screenshots above, two of the values have four decimal places but by default only two decimal places are shown. To always show three decimal places, use the following format string:

0.000

image

Here’s the result:

image

In this case the 0 is a placeholder for a digit that must always be displayed and the . is the decimal separator; three 0s after the . means you always get three decimal places for non-blank numeric values.

Optional digits

You may have noticed in the last screenshot that all numbers show three decimal places, even the value for Pears and the Total. If you don’t want the decimal places to appear – or indeed you don’t want a digit to appear in a particular place if it’s a zero – you can use a # character as a placeholder instead. The following format string:

0.###

…always shows a zero before the decimal separator, but will only show the decimal places if they aren’t zeroes:

image

Thousands separators

If you want to display a thousands separator in your numbers you can use a comma placeholder in your format string, like so:

#,0.###

image

Percentages

If you have values that you want to display as percentages, you can use the % placeholder as follows:

#,0.###%

image

Notice that two things have happened here:

  • A percentage sign has been added to the end of each value
  • The values appear to have been multiplied by 100. They actually haven’t, but the percentage format makes them look as though they have been. Any calculations that reference this measure will still get the unmultiplied value as you would expect.

Currency symbols

If you want currency symbols to appear in your format string you can just add them in either before or after the main part of your format string. For example to put a UK pound sign in a format string you can use the following:

£#,0.00

image

Different formats for positive values, negative values and zeroes

If you need to format positive values, negative values and zeroes differently, you can add up to three different sections to your custom format string separated by a semi colon, as follows:

#,0.0;-#,0.000;0

image

In this case notice how the positive values have one decimal place, the negative value has three decimal places and the zero has no decimal places. In Analysis Services Multidimensional it used to be possible to add a fourth section to format blanks/nulls, but that does not seem to work here unfortunately…

Formatting negative values with parentheses

A common requirement in financial reporting is to format negative values with parentheses (round brackets) instead of a minus sign, and that’s possible with custom format strings. For example:

#,0.0;(#,0.000);0

image

Other Text

You can escape individual characters in your format string by preceding them with a \ placeholder. Say you wanted a # to actually appear in your formatted output and not have it considered as a placeholder, you could use the following:

\##,0.00

image

You can also include whole chunks of text by putting it in double quotes, like so:

“Positive”;”Negative”;”Zero”

image

That’s enough for today; tune in for my next post with even more examples!

You can download the sample pbix file for this post here.

11 responses

  1. Aren’t these all standard formats already in DAX/MDX in Analysis Services? I’m curious when the day will come when we’re no longer building cubes in Analysis Services and using Direct Query, with some of those limitations, but rather we are building our dimensional data model’s foundation directly within PowerBI for Enterprise Class BI (dozens of Measures and FACTs) and retiring SSAS.

    • Yes, although I understand there are some differences between the format strings supported in SSAS and those supported in Power BI (the blank/null formatting support that I called out in the post is one example). It’s certainly our – and I mean Microsoft’s – intention that Power BI will become a superset of all SSAS functionality, so if you’re already using Power BI you won’t need to buy SSAS separately. We’re not quite there yet, but it’s happening.

  2. Pingback: Power BI - Benutzerdefinierte Formatzeichenfolgen für Zahlen - thinkBI

  3. A typo Chris
    in the section – Different formats for positive values –

    “it used to be possible to add a fourth section to format blanks/nulls, but that does seem to work here unfortunately…”

    does NOT seem to work here??

  4. Thanks Chris. Very helpful. Can you help me understand the logic of the comma formatting. You have shown #,0.0. Yet I have always thought I needed to do #,##0.0. Have I been doing it with unnecessary padding all this time?

  5. If you have a measure like the one below will this new format allow you to have different formats depending on which number is actually returned – so you can have Gross Margin returned as a percentage?

    Sales Value=
    IF(
    HASONEVALUE( Calculation[Calculation] );
    SWITCH(
    VALUES(Calculation[Calculation]);
    “Revenue”; [Sales Revenue];
    “Gross Profit”; [Sales Gross Profit];
    “Gross Margin”; DIVIDE([Sales Gross Profit]; [Sales Revenue])
    )
    )

  6. Pingback: Custom Formatting Numbers in Power BI – Curated SQL

  7. Pingback: Power BI Custom Format String Examples, Part 2: Dates And Times « Chris Webb's BI Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: