Formatting text

1. Overview

This article shows you how to format how data is displayed as text in visualizations or their tooltips:

• Metric sets determine how to format numbers (measures) and unknown members, for example the number of decimal places.
• Time dimensions have customizable formatting for each time period/level.
• Visualizations can combine multiple values together with predefined text and can also change number and date/time formats.

Related video: Using The Data Analysis Panel

2. Metric set element format

You can format a measure displaying numeric data by changing the metric set while it's checked out to you.

In the Data Analysis Panel, click the green tile for your measure to edit its settings, then scroll down and click Define text formatting.

This opens the Configure Formats dialog, containing multiple formatting options.

To set up the main formatting, click to Set format. If previously set, this button may display the measure's current format as its text (e.g., N2).

Using the Format Type dropdown in the Configure Format dialog:

• Choose a standard format type like Number or Currency with options such as the number of decimal places. These are designed to automatically display numbers as expected for each user's culture.
• Or choose Custom for more advanced formatting with more control over the result. Enter any custom format or choose one of the common examples from the dropdown menu to the right. (Thousands and decimal separator characters are always determined by the user's culture and not changed by any format settings.)

Some common examples of formats are listed below. For more details, see .NET standard numeric format strings and custom numeric format strings.

When the Use Source Formatting option is selected at the top of the Configure Formats dialog, the formatting comes from the underlying data source such as a data cube. This is normally unchecked automatically after you have customized the formatting in your metric set.

2.1. Formatting currency

Currency formats can automatically determine where to place the currency symbol according to the user's language and culture settings, as well as the currency symbol itself if you did not set one.

Normally, your values are in a specific currency and you should enter the corresponding Symbol. It will be placed according to each user's culture, but the correct symbol will be displayed for all users.

If you prefer to format a value with a specific symbol placement that does not change based on culture, use a non-currency format such as Numeric or 0.00, and specify the placement using the Symbol Placement setting.

2.2. Formatting large values

Numeric values can be further formatted into thousands, millions, billions, or trillions units automatically according to the magnitude of the values as they change.

For example, ensure Additional Format Settings is expanded, and enter 0K as the Thousands Format. 0 is a placeholder for the corresponding number of thousands, and this displays values larger than one thousand with a K symbol following an appropriately divided value,

If the Format Large Values Separately option is selected, numbers in different rows or columns can be formatted into different magnitude units as they vary. When this option is unchecked, all numbers displayed at one time use the same unit for easier comparisons.

2.3. Formatting ranges

You can assign different formats to different ranges of values, for example to reduce the number of decimal places for smaller numbers.

To format a specific range, click to expand Range Format Overrides in the Configure Formats dialog, then click Add range format override.

In the Configure Range Override Format dialog for each range, all of the format options described in previous sections are available. Set From and To to determine the value range this formatting is applied to.

2.4. Formatting unknown values

The caption Unknown displayed for unknown values of a hierarchy can be customized:

Click the orange tile for your hierarchy in the Data Analysis Panel, and set the Unknown Member Caption. In newer versions:

1. Click Customize to set a custom caption.
2. Enter your custom caption into the text box, or leave it empty to display it that way.
3. If you want to display the default caption again, click the delete icon.

When using a missing data rule on a measure with an Output Rule of Empty, there is an Empty Value Text setting that can display text in empty cells instead of leaving them blank.

3. Time dimension formatting

When working with date & time data, it's common to apply a time dimension to group by time periods such as Year and Month, drill down, and more. Time dimensions can be applied to your data in a data cube or in a metric set.

Time dimensions also have formatting options you can customize to change how the dates & times are formatted as text when displayed in your metric sets and visualizations. For details, see Create a time dimension.

4. Placeholder keywords

Various text and tooltip properties of data visualizations can be set with placeholder keywords that will be replaced with actual values from your data when displayed. These property settings are often prepopulated for you using the data assigned in Data Analysis Panel's Visualization tab, but you can optionally customize this property yourself for more options, more control over the formatting, or to combine the keywords with descriptive text.

When you click into one of these properties in the Properties window, a popup will appear suggesting all of the available keywords described below. Click one to insert it into the property at your current position in the text.

You can insert multiple keywords and combine them with any other text or characters you choose to type in.

The most common keywords refer to a measure or hierarchy from the Data Analysis Panel to display its values, enclosing its caption in square brackets. For example: [Order Quantity], using the format defined in the metric set or the time dimension by default.

4.1. Formats

You can optionally edit the keywords used in the Properties window to customize the formatting for numbers or dates/times to be different than what is set up in the metric set or time dimension.

The example below shows a custom format set for the Order Quantity measure displayed in a chart's data point labels:

To format a keyword, add a colon and your format before the square closing bracket. For example, to format [Order Quantity] as numeric with no decimal places, change it to [Order Quantity:N0].

When you include the optional format, placeholder keywords support most standard date and time format strings, custom date and time format strings, and standard numeric format strings. (Custom numeric format strings are not supported in visualization keywords.) To customize a hierarchy's formatting, its data type needs to be numeric, date/time, or a time dimension hierarchy, and it should not be placed on Slicers in the Data Analysis Panel.

Examples of numeric formats you can use:

Standard date and time formats are specified by a single character and place their date and time components automatically according to the culture of each logged-in user:

Custom formats allow you to place each date and time component yourself. An example of a complete custom format is yyyy-MM-dd.

4.2. Filter and level keywords

By default, visualization tooltips identify the data by its hierarchy caption, for example the caption Date regardless of whether the hierarchy level Year or Month is displayed from a time dimension.

If you want to display the caption of the hierarchy level, use the keyword [<Hierarchy> Level], replacing <Hierarchy> with your hierarchy name. For example: [Date Level].

You can display filter values for data placed on slicers as a regular keyword.

When using a slicer comparison measure, you can also display the filter values determining the 'sliced' measure values using the keyword [<Sliced Measure> <Slicer Hierarchy> Filter]. For a slicer comparison measure with the caption Sliced Sales Amount filtered by the slicer Country, for example, you can display the particular country used to filter the sales amounts using [Sliced Sales Amount Country Filter].

The following keywords can be used in text properties when using a Dynamic Element filter, replacing <Dynamic Measure> or <Dynamic Hierarchy> with the name of your particular dynamic measure or dynamic hierarchy:

4.3. Visualization keywords

In addition to measure and hierarchy keywords, the following keywords may be available depending on the visualization, and will be suggested in the popup when you edit a text property:

4.4. Size and color rule keywords

The following keywords are supported for the legend text property of size and color rules (i.e., Range Size Rule, Auto Size Rule, Range Color Rule, and Auto Color Rule) in any visualization.

5. Format properties

Dedicated format properties in data visualizations such as on chart axes and gauge scales support the same formats as visualization keywords (and have the same restrictions).

Axes and scales have a Symbol property below the format similar to the settings on measures. When applying currency formatting, it is best to set this to the currency symbol for your data as shown above for measures so that it is displayed consistently for users logged in with different language or culture settings.

6. See also

• Video: Using The Data Analysis Panel
• Transform: Process Result
• Localization and multi-language support
• Create a time dimension
• Metric set analysis tools
• Using chart properties
• Using a table visualization
• Setting up the visualization