Formatting Text

This applies to: Managed Dashboards, Managed Reports

You can 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 also change number and date/time formats.

Related video: Using The Data Analysis Pan

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.

Configure format

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

To set up the main formatting, click to Format button, which displays the measure's current format as its text (e.g., N2).

Using the Format Type drop-down 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 drop-down menu to the right. (Thousands and decimal separator characters are always determined by the user's culture and not changed by any format settings.)

Entering a custom format

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

Format Type	Format	Sample Text
Numeric with 5 decimal places	N5	80,487,704.17919
Currency with 2 decimal places	C2	$80,487,704.18
Percent (multiplied by 100) with 0 decimal places	P0	8,048,770,418%
Exponential/scientific with 2 decimal places	E2	8.0SE+007
Custom numeric (1 non-zero decimal place)	0,0.#	80,487,704.2
Custom numeric (thousands)	#,0,K	80,488K
Custom numeric (millions with 1 decimal place)	#,0,,.0M	80.5M

Note: You can also set the format for a measure in a data cube ahead of time so that it takes effect by default in every metric set where it's used.

Formatting Currency

When using a currency format on its own, the user's language and culture settings determine which currency symbol to use and where to place it.

Currency and symbol placement as determined for a French user

Important: Allowing the currency symbol to change automatically in format settings does not change the numeric values. Unless you store or convert to different currencies elsewhere, users with different cultures may see the same numeric value with different currency symbols unless Symbol is set.

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 will be displayed for all users.

Values in dollars displayed for a French user

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.

Specify the symbol placement for non-currency formats

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, to display a value larger than one million with an 'M' symbol following an appropriately divided value, enter OM into the Millions Format field. The '0' is a placeholder for the corresponding number of millions.

Specify the format for magnitude units

Note: Chart axes and gauge scales do not display measure values directly but their own reference values. They have their own versions of these properties that can be set in the Properties window.

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 comparison.

Values formatted using common unit of thousands

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.

Add a 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.

Range format override

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. See Create a Time Dimension.

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 Properties, 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.

Customizing the tooltip text for a series of chart data Points

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.

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 set up in the metric set or time dimension. The figure below shows how to format the Order Quantity measure displayed in a chart's data point labels:

Customizing the format for a measure keyword in data point label text

Note: You can usually format the data in the metric set or by using a time dimension rather than formatting measure and hierarchy keywords.

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:

Format Type	Format
Numeric with 2 decimal places	N2
Currency with no decimal places	C0
Percent (multiplied by 100) with 1 decimal place	P1
Whole numbers, 3 digits	D3

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:

Standard Format	Specifier	Sample Text
Short date	d	6/12/2015
Long date	D	Friday, June 5, 2015
Short time	t	8:31 AM
Long time	T	8:31:05AM
Long date, short time	f	Friday, June 5, 2015 8:31 AM
Long date, long time	F	Friday, June 5, 2015 8:31:05 AM
Month/day	M	June 05
Month/year	y	2015 June

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

Custom Format	Specifier	Sample Text
Year	y, yy, yyyy	5, 05, 2005
Month	M, MM, MMM, MMMM	4, 04, Apr, April
Day	d, dd, ddd, dddd	9, 09, Wed, Wednesday
12-hour clock	h, hh	9, 09
24-hour clock	H, HH	9, 09 & 21, 21
Minutes	m, mm	6, 06
Seconds	s, ss	8, 08
AM/PM	t, tt	a, AM
Fractional seconds	f, ff, fff	6, 61, 611
Literal text	'My Text'	My Text

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].

Tooltip displays the hierarchy 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:

Keyword	Description
[<Dynamic Measure> Caption]	The caption of the actual measure currently displayed by the dynamic measure.
[<Dynamic Measure>]	Represents the numeric values of the current dynamic measure.
[<Dynamic Hierarchy> Level]	The caption of the actual hierarchy or hierarchy level displayed by the dynamic hierarchy.
[<Dynamic Hierarchy>]	Represents the captions/values of the current dynamic hierarchy.

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:

Keyword	Visualization	Description
[Total]	Stacked and 100% stacked charts, pie charts	Displays the stacked total up to and including that data point from zero or a pie's start angle.
[Percent]	100% stacked charts, pie charts	Displays the percent of the data point's value out of its stack total.
[Measure Name], [Measure Value]	Charts or tables displaying a transposed metric set	The measure name or value displayed by each data point or in each row.
[Member]	Pie chart, waterfall chart. charts with Hide Total Values unchecked, tables (custom total text), treemaps, tree diagrams, relationship/Sankey/chord diagrams (nodes)	The hierarchy member caption. In a table's custom total/subtotal text, displays the hierarchy's All caption, which can be set in a data cube's Process Result.
[Level]	Treemaps, tree diagrams, relationship/Sankey diagrams (nodes)	The hierarchy level caption.
[Size]	Treemaps	The value of the measure used to determine the node size.
[SizeMeasure]	Treemaps	The name of the measure used to determine the node size.
[Flow]	Sankey diagrams (nodes)	The total quantity flowing in or out of the node.
[Source]	Sankey diagrams (links), chord diagrams (links)	The source hierarchy member caption.
[SourceValue]	Chord diagrams (links)	The quantity of the flow from the source to the target.
[Target]	Sankey diagrams (links), chord diagrams (links)	The target hierarchy member caption.
[TargetValue]	Chord diagrams (links)	The quantity of the flow from the target to the source (if applicable).
[Value]	Chord diagrams (nodes)	The total quantity flowing out of the node.

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.

Keyword	Description
[From]	The starting value in the range.
[To]	The ending value in the range.

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).

Using a date/time format string for an axis label Format property

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.

For more information, see: