Value formats

A value format controls how a column's or a measure's values are displayed: the format choice, the currency, the decimal places, the thousands separator and a custom pattern. One editor serves both columns and measures, and this page is its reference.

Open the format editor

• Columns: select a column in BI tables and use Format in the column toolbar, or open the Columns tab of the BI table popup, where every column has its own Format control. See Semantic models and BI tables.
• Measures: save the measure first, then set its format on the Measure row of the workspace tools bar. See Measures and Measure editor.
• In the popup the controls stack; in the toolbars they sit on one line, where the thousands switch reads 1000s instead of Thousands separator.

Set the format

1. Open the editor on a column or a measure.
2. Choose a format in the Format list; it reads Default until you choose one. The list depends on the kind of values, as in the table below.
3. Set the options the format owns.
4. Watch the Preview as you edit; it follows every change.

• Currency opens a searchable list of ISO 4217 currencies. The most used ones come first under Popular (GEL, USD, EUR, GBP, CNY, RUB and ARS), then every other currency A to Z under All currencies, and the picker starts on USD. Type a code, a symbol or a name to narrow the list: search ignores capitals and accents, so a name like zloty finds PLN (zł), and No currency found. means nothing matches. A code the catalogue does not know, such as one stored by an older report, stays visible and selected under Current as Not in the catalog.
• Decimals sets the number of decimal places, from 0 to 10. It is hidden when the format is Abbreviated.
• 1000s (labelled Thousands separator where the controls stack) turns the thousands separator on or off. It appears for number kinds only.
• Pattern appears for Custom only: type the pattern the values follow.

Formats by kind

Text columns carry no format, and a rejected column cannot be formatted either; the editor says Rejected columns have no display format.

Patterns and the preview

• A custom pattern is checked before it is applied. A pattern that fails is not committed, and the editor shows Unrecognized pattern. Try tokens like dd, MM, yyyy, HH, mm. Where the controls stack the message stands as a line below the field; in the toolbars it opens as a tooltip.
• The Preview shows one sample value formatted with the current settings. A long sample is truncated, and its tooltip reads Preview: <sample>.
• For dates and times, the pattern that applies is: the pattern you typed under Custom; otherwise the chosen format's own pattern; otherwise a saved custom pattern; otherwise the kind's default pattern.

How values render without a format

• With no format set, values show their kind's defaults: numbers in Plain, booleans as True or False, dates as dd/MM/yyyy, date and time values as dd/MM/yyyy HH:mm:ss, times as HH:mm:ss, and text unchanged.
• An empty value shows as empty.
• Numbers keep their exact digits, even very large ones.
• A currency format without a currency falls back to USD; if the currency formatting fails, the plain number shows instead.
• Abbreviated numbers take a suffix that scales with the value: K, M, B or T.
• Percentage shows a percent sign; an exact zero shows 0%.
• Clock durations read as HH:MM:SS; Compact durations read as 1h 23m, 5m 2s or 45s.

Limits and saving

• Decimals stops at 10 here. On a visual, the separate value format on the Visual tab stops at 6.
• Each format owns one option: Currency owns the currency, Custom owns the pattern. Switching to another format clears the option the previous format owned.
• A pattern that does not validate is not saved.
• Formats are saved with the model: a column's format stays with the column, and a measure's format applies to every report that uses the measure.
• Value formats are not cell formatting. To paint cells and tint columns on a Table or Matrix, see Cell and column formatting.

Questions, answered

Why did changing the format clear my pattern or currency?

Each format owns one option: Currency owns the currency and Custom owns the pattern. When you switch to another format, the option that belonged to the previous format is cleared.

Why can't I format this column?

Only typed columns carry a format. Text columns have none, and a rejected column is refused with Rejected columns have no display format. Choose a different column.

Why is the Decimals field missing?

Decimals is hidden while the format is Abbreviated. Choose another format to set decimal places.

Can I set more than 10 decimal places?

No. This editor stops at 10. The value format on a visual's Visual tab is a different setting and stops at 6; see Format a visual.

Which currencies can I choose?

Currency opens a searchable list of ISO 4217 currencies: the most used ones first under Popular, then every other currency A to Z under All currencies. Type a code, a symbol or a name to narrow it; search ignores capitals and accents, and No currency found. means nothing matches. The picker starts on USD, and a code the catalogue does not know stays under Current as Not in the catalog.

What does the Abbreviated format show?

A short number with a suffix that scales with the value: 1.2K, 3.4M, and larger values up to T. Choose Plain to see the full number again.

What do the parts in a format summary mean?

They join with ·: the kind of values first, then the non-default choices, such as the format, decimals as N dp, the thousands separator as 1,234 sep., the currency code and a custom pattern. For example Whole Number · Currency · USD. A rejected or unknown column reads -.

What does the Preview show?

One sample value of the column's kind, formatted with your current settings. It follows every change, so you see the effect of each choice as you make it. A long sample is truncated; hover it to read the full sample.

What happens when my custom pattern is not valid?

It is not applied. The editor shows Unrecognized pattern. Try tokens like dd, MM, yyyy, HH, mm. Correct the pattern, and it applies.

Does the format apply to every report, or only this one?

Everywhere the column or measure is used. A column's format is saved with the model, so every visual that reads the column shows it, and a measure's format applies to every report that uses the measure.

Why does my chart show values in another format?

A chart can carry its own value format, set under Values on the Visual tab, separate from the column's or measure's format; the chart-side controls are on Format a visual, and the tile types on Visuals.