Limited screen real estate is one of the biggest challenges for developers of dashboards and mobile BI applications. This challenge comes to the fore when the metrics in the datasets underlying the dashboard graphs represent numbers in the millions and billions. When this is the case, the plotted metric figures can steal a significant percentage of a graph’s real estate.
In addition to stealing screen real estate, it also causes users to spend time calculating how many commas are in the metric figures, instead of focusing on the story the data is telling and making decisions based on it.
A simple solution that addresses this challenge can be found in MicroStrategy’s custom formatting feature. This solution dynamically abbreviates metrics in the billions (1.0B), millions (1.0M), and thousands (1.0K) - it also helps regain the screen real estate and provides easier readability of the metric values as illustrated by the images below.
Before using MicroStrategy custom formatting strings:
After using MicroStrategy custom formatting strings:
To apply this solution, follow the step-by-step instructions below or scroll down to the bottom of the page to watch a video on how to apply MicroStrategy custom formatting strings to metrics.
1. Log on to MicroStrategy Web and open the report you wish to apply the custom formatting. Do not use MicroStrategy Developer to implement this solution as there is a limitation with the number of custom formatting conditions. For known bugs and limitations regarding the use of MicroStrategy's custom formatting feature, see the Helpful Links section at the bottom of this page.
2. Right click on the Metric header, select Advanced Formatting.
3. Navigate to the Number tab, under the Category: section, select Custom, delete the text under the Type: field.
4. Copy and paste one of the Custom Formatting Strings below that matches the formatting you're looking to apply.
5. Click OK and the Metric values should dynamically abbreviate.
MicroStrategy Custom Formatting Strings:
For revenue-based metrics with a dollar ($) sign and positive values ($1.0B, $1.0M, $1.0K, $0):
[>=1000000000] "$"#.#,,,"B";[>=1000000] "$"#.#,,"M";[>=1000] "$"#.#,"K";"$"0
*** To change the currency from USD ($) to any other currency such as EUR (€), CAD (C$), GBP (£), BRL (R$) or INR (₹), replace the $ sign inside the double quotes with sign of the desired currency.
For revenue-based metrics with a dollar ($) sign and negative values (-$1.0B, -$1.0M, -$1.0K, $0):
[<=-1000000000] "$"#.#,,,"B";[<=-1000000] "$"#.#,,"M";[<=-1000] "$"#.#,"K";"$"0
For metrics without a dollar sign and positive values (1.0B, 1.0M, 1.0K, 0):
[>=1000000000] #.#,,,"B";[>=1000000] #.#,,"M";[>=1000] #.#,"K";0
*** To change the number format from thousands (K) to thousands (T) or from million (M) to million (MM), replace the letter K inside the double quotes to T or the letter M to MM.
For metrics without a dollar sign and negative values (-1.0B, -1.0M, -1.0K, 0):
[<=-1000000000] #.#,,,"B";[<=-1000000] #.#,,"M";[<=-1000] #.#,"K";0
How to Apply MicroStrategy Custom Formatting Strings to Metrics Video
