Excel and ‘Percent’ Formats for Calculated Measures

Today I was with a customer and created a calculated measure that was formatted as a percentage. The formatting showed up fine in the cube browser but did not in Excel, which was a bit strange given that other percentage calculated measures on the cube seemed to be working fine. Now I knew that Excel doesn’t pick up the formatted_value of a cell in a cellset, but instead takes the format_string associated with each cell and interprets this as an Excel format inside a pivot table, and this explains why sometimes SSAS formats don’t work in Excel. So clearly something was going wrong with Excel interpreting the format string I’d defined.

Finally the guy I was working with, Andrew Baker, worked it out. Look at the two following calculated measures:


When you add them to a cube and browse the cube in the cube browser, you see this:

In Excel you see this:

What’s the difference? Yes, you guessed it, when it comes to the built-in SSAS format string types like ‘Percent’, Excel is case-sensitive. So a format string like ‘PERCENT’, while it’s valid from an MDX point of view, gets ignored by Excel whereas ‘Percent’ is correctly interpreted as a percentage format.


7 thoughts on “Excel and ‘Percent’ Formats for Calculated Measures

    1. I greatly appreciate this post. I would also appreciate it if ReportBuilder would do the same thing (utilize Fields!(“FORMAT_STRING”) automatically as the format of the tablix cell!

  1. It would be great if you could investigate deeper into formatting. I have different issues making formats work in Excel. It seems to be dependent on user locale or something like that, since on some computers things like “0,00#” work, on others no formatting is applied.

Leave a ReplyCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.