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:
I’m going to create a whole series of identical measures defined like this:
[sourcecode language='html' padlinenumbers='true']
SalesEg1 = SUM('ExampleTable'[Sales])
[/sourcecode]
…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:
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
Here’s the result:
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:
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.###
Percentages
If you have values that you want to display as percentages, you can use the % placeholder as follows:
#,0.###%
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
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
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
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
You can also include whole chunks of text by putting it in double quotes, like so:
“Positive”;”Negative”;”Zero”
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.
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.
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??
Corrected, thanks
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?
Yes, I think so!
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])
)
)
No, but in the long run calculation groups will be the solution for the problem you’re trying to solve here and they do support dynamic format strings: https://docs.microsoft.com/en-us/analysis-services/tabular-models/calculation-groups#dynamic-format-strings
Hi Chris,
I am using the October 2019 release (2.74.5619.862 64-bit) of Power BI Desktop and can see how you can customise *field* formats via the Data Model view but not measures. Could you clarify whether this custom formatting also applies to Measures and if so, how to navigate to it?
Yes, it applies to measures too. Just select the measure in the diagram and the same options appear as for a field.
Brilliant – thanks Chris! I was thrown by the different sort order of my Measure-only tables and didn’t think they were available in that view. Thanks for such a quick response and such a comprehensive blog in general.
Hi Chris,
I use the formatting for measures but have an issue by changing the measure. If I confirm the change the formatting will be reset to a default system format. Is this normal behavior or have I an issue in my format string?
0″‘”0##;-0″‘”0##;0″‘”0## -> 123’456
When I use “0.###” and the value being formatting is 7 it returns “7.” which correctly does include any digits to the right of the decimal point, but strangely does include the decimal point itself (the dot).
Any idea why that is happening or how to prevent it?
That should say “correctly does NOT include any digits to the right of the decimal point”
I don’t know how to prevent the decimal point appearing, unfortunately. I think your only option is to use a format like 0.000 to make sure you always show the three digits to the right of the decimal point.
Oh well. I ended up first checking if it is a whole number and using a separate formatting string (with no dot) in that case.
Unfortunately I’m deferring to the same workaround too.. How to make that decimal place conditionally disappear without bloating up the DAX statement…