Number formats
A number format decides how a value is written. Set it in the Start tab's Number section for
columns or selected cells. Numbers, dates and the separators follow the language of the report (German 1.234,57, English
1,234.57).
The default: Power BI
A column nobody formatted uses Power BI: the format the data source brings along (the "format string" of the field or
measure). If there is none, the value is shown as a plain number in the report's language, rounded to a whole number -
1234.5678 shows as 1,235 in English. Increase Decimal and Decrease Decimal work on top of it and give the number
decimals.
Categories
| Category | Shows | Options |
|---|---|---|
| Power BI | The data source's own format | Decimals (Increase / Decrease Decimal) |
| Dynamic | For a measure with a dynamic format string: the format follows the value, as the measure's format string says. The grand total is written as that string, the other values as Power BI does. Only offered for such a measure; it is the category such a measure starts on. | |
| Number | A plain number with a fixed count of decimals (2 unless set) | Decimals, 1000 separator (off by default), Negative numbers (minus or parentheses), display units |
| Currency | An amount with its currency symbol, placed the way the report's language writes it (1.234,50 €, $1,234.50) | Decimals (the currency's own, for example 2 for the Euro and 0 for the Yen), Currency, negative numbers, display units |
| Accounting | A currency amount with negatives in parentheses and zero as a dash | Currency, decimals, display units |
| Short Date | Day, month and year in the report's language (31.12.2024, 12/31/2024) | |
| Long Date | Weekday, day, month and year written out | |
| Time | Hours, minutes and seconds (13:45:07, 01:45:07 PM) | |
| Comma | A number with thousands separators, negatives in parentheses, zero as a dash | Decimals, display units |
| Percentage | The value times 100, with a percent sign (25,60 %) | Decimals (2 unless set) |
| Fraction | The closest fraction, 3 1/4 | Denominator: up to 1, 2 or 3 digits |
| Scientific | A mantissa and a power of ten, 1,23E+04 | Decimals (2 unless set) |
| Text | The value as it comes, with no number format | |
| Link | The value is a web address: shown as an icon, a replacement text or the address, and a click opens it | See below |
| Custom | A Power BI / Excel format string, for example #,0.00 "EUR" | The string; Increase / Decrease Decimal rewrite its decimals |
Dates and times
A date value is written as the category says. A number is read as an Excel date number (days since 1900; the part after the
decimal point is the time of day). A text that looks like a date (2024-12-31) is read as a date, written as it stands - not
moved by a time zone. A text that is no date is shown as it is. A blank stays blank.
Display units
For Number, Currency, Accounting and Comma, the value can be shown divided by 1,000, a million, a billion or a trillion, with a unit after it:
| Unit | English | German |
|---|---|---|
| Thousands | K | Tsd. |
| Millions | M | Mio. |
| Billions | B | Mrd. |
| Trillions | T | Bio. |
2,500,000 in millions with a Euro currency is 2,50 Mio. € in German and $2.50M in English. Percentage has no display
units.
Currencies
The dialog offers: Euro (EUR), US Dollar (USD), Pound Sterling (GBP), Swiss Franc (CHF), Yen (JPY), Yuan (CNY), Swedish Krona (SEK), Norwegian Krone (NOK), Danish Krone (DKK), Zloty (PLN), Czech Koruna (CZK), Forint (HUF), Canadian Dollar (CAD), Australian Dollar (AUD), Indian Rupee (INR), Real (BRL), Mexican Peso (MXN), Turkish Lira (TRY), Rand (ZAR), Won (KRW) and Dirham (AED). Until you choose one, the currency of the report's language is used (EUR for German, USD for English (US), and so on).
Link
The value must be a web address. In the dialog, Show picks how it appears:
| Show | Cell content |
|---|---|
| Icon | One of seven icons: Chain, External link, Arrow, Chevron, Globe, Document, Info |
| Replacement text | Your text, for example "Open" (underlined by default) |
| The URL itself | The address (underlined by default) |
A click on the cell opens the address. A cell with no value shows nothing. The link color is the link blue (#0563C1) until you
choose one - and a font color set on the column wins over the default blue. Copy pastes the address as plain text and as a
hyperlink with the replacement text. Set Width > Auto measures an icon as 16 px, a text as text.
Custom format strings
A custom string is handed to Power BI's formatter with the report's language, so the syntax is the one of Power BI and Excel. The dialog offers examples with one click:
| Example | Shows |
|---|---|
#,0.00 | Thousands separators, two decimals |
#,0.0 "EUR" | One decimal and a fixed text |
0.0% | A percentage with one decimal |
0.00E+00 | Scientific |
#,0;(#,0);"-" | Negatives in parentheses, zero as a dash |
An empty custom string is the default (Power BI).
Increase and Decrease Decimal
They start from the decimals the values show now and go one up or down (0 to 20). They do nothing where decimals mean nothing - dates, times, text, links, fractions, a custom date format - and are greyed out then.
The Number Format dialog
Start tab > Number > More Number Formats... - see Start tab: Number. The Value Format button of the Chart tab uses the same format as the column.