Custom Formatting Strings

Formatting metrics is something that we’re all familiar with.  Currency, commas, decimal places and percents are common choices, but what happens when we have to do something more advanced?  The Number Formatting screen for a Metric contains an option to provide a Custom Formatting string, but documentation is sparse on this hidden gem of a feature.  Today, I’ll talk a little bit about how it works and provide some useful links for crafting your own formatting magic.

To start, to access the Custom Formatting box, simply right click a Metric and choose Formatting -> Values.  Under the Number tab, Custom is the last option in the list.

This text box can hold a Custom Formatting String that will apply to the values in the metric.  You can embed some basic logic into it, and it follows the same syntax of Microsoft Excel’s Custom Formatting syntax.  For more some good documentation on the different things you can do, check out Microsofts article on the subject:

One particular use case that I wanted to highlight was a requirement to show values as 3.5K instead of 3,500 and 4.6M instead of 4,600,000 in a Graph’s axis.  At first I attempted to solve this with Conditional Formatting (and was successful), but unfortunately that formatting didn’t carry over into the Graph’s Axis. A colleague of mine found the above linked Custom Formatting document and managed to craft this handy Format:
To break it down, you can think of this String as a Case statement with 3 possibilities, each separated by a semicolon.




Within the first 2 statements, the condition within the brackets gives the condition that we’re catching, in this case, when the value is a million or a thousand.  The formatting string #.#0, tells it that we want to format it as a number (#) with a decimal point (.) that includes 0 instead of rounding it off (0) and separate larger numbers with commas (,).  Finally, we append some free text within the double quotes to denote M or K.  The final 0 simply catches the formatting for the 0 value.

Sample data from MicroStrategy Tutorial

Ultimately, this solution worked great and we were able to meet the requirement of formatting the Graph Axis in this manner.  In our case, this was a proof of concept for a complicated report that we ended up doing in Flash Builder, but we do still have some old exported PDFs that we emailed to users that showed it was working.  I mention this, because when I tried to reproduce it again for the purposes of this post, I was not able to.  It seems others in a forum post asking about this feature today (which jogged my memory that we had done this and lead to this post) also had similar issues.  Not only was I not able to reproduce it in a Graph, but in Web the format string didn’t even work in a Grid.  I’ve reached out to MicroStrategy Technical Support, and will update this post when a workaround is discovered or a patch is released.

In the meantime, mastering the Custom Format String can open a lot of opportunities for meeting requirements and making your numbers look a little better.  Just don’t try to use them in a Graph Axis … yet.

You may also like...

32 Responses

  1. srk says:

    I wish i had known this before. Spent a lot of time for one of our custom formatting requirement, and finally we had to do it at the database side. Referred to help doc, but was not helpful… many thanks for sharing this!!

  2. iliyas says:

    It’s not working for million, but it is working for thousand.

    OrginalValue FormattedValue
    10000 10.0K
    1000000 100.0M (Which is incorrect)

  3. Hi Bryan,

    It is nice solution.

    Could you please explain me for this line “and separate larger numbers with commas (,). “

    If possible thn please provide small example.


    • Bryan says:

      There’s a link there that goes into Microsoft’s documentation that has very in depth details, but for this one:

      “A comma that follows a digit placeholder scales the number by 1,000. For example, if the format is #.0,, and you type 12,200,000 in the cell, the number 12.2 is displayed.”

  4. Hi Bryan,

    Thank you for your reply.


  5. Song Gao says:

    Hi Bryan,
    It is a greate solution.
    But as for the Graph Axis, it works for single axis, when it comes to dual axis, for example, Y1 for Revenue and Y2 for Revenue Mix(%), then the “$” or “K” or “M” will not show as expected.

    Any idea and way to fix it?


  6. sravan says:

    Hello ,

    I’m able to bring K or M on the report but failed to bring the dynamics units (K or M ) on graph.Can any one throw some light on this


  7. kiran says:


    The above formatting when applied in Grid’s can be visible, but when tried to apply the same formatting in Dashboards, we are not able to see that effect.
    In dashboarding do we need to do any additional operations to achieve this?

    • Bryan says:

      Where are you trying to apply it? I commonly use it on text boxes and it works fine.

    • Hi Bryan,

      as per your comments i did the change on Text Box but its not applying, when i check in report its working fine, please let me know any other way where we can achieve this-thanks


  8. ★fire says:

    Hi Bryan,

    This formula works perfectly for us on reports, dashboards, charts. But it fails for negative numbers. Any idea how to make this work for both positive and negative numbers?


  9. Saumya says:

    Can anyone suggest a work around for negative numbers? I tried using logical operator “OR” but its not working; it picks only the first condition.

  10. Sneha says:


    I have used Custom formatting ####-##-## to display number 20140220 as 2014-02-20. Using MicroStrategy 9.3.1, it works well in desktop and web, but does not reflect in iPad.

    Can anyone suggest?

    • Saurabh says:

      Hi Bryan,

      Thanks for the post. I tried the solution it and works perfectly for the Grid reports. When I try to convert the report to a graph the formatting is not available. In the graphs axis scale formatting as well I don’t see the option for custom formatting. Reading the posts on Microstrategy’s forum I saw it being stated as a bug in 9.2.1 . I am on the same version. Has it worked for graphs formatting for anyone on 9.2.1.


  11. Jennifer says:

    Does anybody know how to do this for Nulls? I would like to replace null fields with “N/A”. Thanks much!

  12. Vikram says:

    Hi Bryan,

    Is there any limit for the number of conditions in custom formatting
    Looks like it is taking 4 conditions and ignoring the rest
    Original Numbers
    2,067,551 -747,144 -843,649 841,082
    [>=100000000]#,###,,;[>=10000000]#.0,,;[>=1000000]#.00,,;[>0]0.000,,;[=100000000]#,###,,;[>=10000000]#.0,,;[>=1000000]#.00,,;[0]0.000,,——interchanged the last 2 conditions
    2.07 (0.747) (0.844) 841081.838—————Last condition not working

    • Vikram says:

      Hi Bryan,

      Is there any limit for the number of conditions in custom formatting
      Looks like it is taking 4 and ignoring the rest
      Original Numbers
      2,067,551 -747,144 -843,649 841,082

      [>=100000000]#,###,,;[>=10000000]#.0,,;[>=1000000]#.00,,;[>0]0.000,,;[=100000000]#,###,,;[>=10000000]#.0,,;[>=1000000]#.00,,;[0]0.000,,——interchanged the last 2 conditions

      2.07 (0.747) (0.844) 841081.838—————Last condition not working

  13. Cosmin says:

    Hi Brian,

    Can you please help me with this issue? I have this number: 47,860.3 and I want it to be 4,786. The only thing I can think about it is to put #.00#,% in custom, but I don’t want to have % at the end of the number. So I need to have the number divided by 10. Thank you !

    • bryan says:

      So take off the %, shouldn’t need it.

      • Trevor Helm Campbell says:

        Regretfully, without the % sign, it does a factor of 1000 instead of 10. If you remove the % sign, the example above becomes 47.860, and not the desired 4,786.

  14. Vikram says:

    Hi Bryan,

    Can you please help me with this issue…..
    My requirement is every number should be divided by Million and number less than Million should show two decimals.
    The custom format I used is [>=1000000]#,###,,;[>=0]0.00,,;
    But the number 998459 is displaying as 1.00 which is supposed to display as 0.99
    Looks like Microstrategy is rounding off the number to 1 after division by Million and not taking the format of >=Million.
    Is there anyway that I can fix this?

  15. vamsi devineni says:

    HI Bryan,

    Is there any limit for the number of conditions in custom formatting

  16. Anna says:

    Hi Bryan,
    I’m trying to display metric which base on fact column with float(126) data type. Microstrategy by default display this metric in following format 5.123456789E-015. Is there any way to format metric to display all decimal places(without E)?

  17. Wily says:

    First, for Jennifer from June of 2014, you can use [=Null] as your condition and then follow it up with the desired format.

    Then, I would like to know if Aditya (from November 2013) or anyone else has made the conditional formatting work for positive and negative numbers (aka absolute value). I’m looking for something like … [ABS()>=1000000000] {format}; [ABS()>=1000000] {format}; … Let me know if anyone can make it happen. Thanks.

  18. Kom says:

    Hi Bryan,

    Would you be able to tell if we have an update yet from MicroStrategy Technical Support on the custom formatting in graphs.

    I am currently working on 2 dashboards, and 1 of them picked up the custom format from the grid and displays as desired (10K, 20M etc) on Y axis. It does it for all the graphs in that dashboard.
    However, the 2nd dashboard is not behaving the same. The grid view does show the custom format but the Y axis doesn`t.
    Its difficult to understand why is this happening.
    Would you pls be able to comment on that?

  19. Trevor Helm Campbell says:

    Looks like there is a tech note about this being a bug in MicroStrategy:

  20. John McEniry says:

    1) In addition to the formatting of [>=1000000]#.#0,,”M”;[>=1000]#.#0,”K”;0

    2) I would also like to display negative numbers as Red with a bracket around the value for example (1.35)M. The (1.35)M would be in red.

    I would also like to be included in the first formula.

  21. Prarthana says:

    In MSTR 9.4.1, we can apply formatting of thresholds on Graph axis. i.e. The requirement was to show the values in “K” or “M” or “T” or in “B” as and when the data changes. This was achieved by applying below two thresholds on metric-
    1) <= 999999999, show,[999999]0.0,,”M”;0
    2)>1000000000, show,[999999999999]0.0,,,,”T”;0

  22. Selva says:

    Hi Bryan,

    This post is very useful for me. Thank you.