Greetings, fellow data analysts!
Management information is not about the decimal places – control¬ling is not accounting. Decision-oriented management reports make an impression when they report on an appropriate scale, i.e. in thousands or millions, whichever applies. That way, numbers are shortened to the key digits, making them easier to read and understand. We explained how this can be done in DeltaMaster some time ago. In DeltaMaster 6, it is even more elegant: The “whichever” is now dealt with automatically, too. Here, we present the new method and revisit the methods that you already know.
Best regards,
Your Bissantz & Company team
Three methods are available for displaying numbers in abbreviated form to the nearest thousand, million, or billion:
- Automatic compact display
- Formatting and scaling of measures
- Formatting using calculated members
The first method is the most recent in DeltaMaster and the easiest to apply: Just activate the respective option, and the numbers appear in the report or throughout the application in a concise, decision-oriented notation.
You can find a comparison of the display with the numbers written out in full and the new compact display on the next page.
| Numbers written out in full |  |
| Compact with K, M, B |  |
| Compact with apostrophes |  |
The great thing about this automatic feature is that units can be entirely mixed: In the very same table, row, or column, some figures can be formatted in millions, others in thousands or billions, and the very small ones can be written out in full. This may seem surprising at first glance – but you will soon discover how easy on the eye these figures are, and how clear and distinct they look to us when the clutter of the less significant decimal places is removed.
In addition, if reports are scaled insensitively and indiscriminately with just one factor, strange numbers often arise. We don’t talk of 0.1 million if we mean 100 thousand, or of 0.01 million or even 0 million if we mean 10 thousand. In this respect, formatting by DeltaMaster is in line with natural parlance: In the report, the numbers are set out as they would be pronounced in a meeting or on the phone.
The look of figures of different magnitudes can also be interpreted, for example amounts in the low millions and high thousands: The thousands have more digits, more digits means more detail, more detail means smaller values, and small values means less important. The large, important figures are short and simple. This makes the compact notation a wonderful, pragmatic tool for decision-oriented reports.
And the compact notation also makes life easier for report editors, as the formatting automatically adjusts to fit the respective values set out in the report. So if the user drills down from group to business unit, for example, the change of scale is automatically taken into account.
Compact for everyone, more compact for professionals
DeltaMaster provides two display variants, as shown above:
- The variant in thousands (K), millions (M), and billions (B) is the preferred choice for reports that are aimed at a wider audience. The abbreviations are widely used, including outside of controlling, and need no further explanation.
- The variant with apostrophes (one apostrophe for the thousand factor, two for millions, three for billions) is particularly compact. It is quite widespread among professionals: In meetings, it is common for a couple of figures on a flip chart to be appended with a little stroke, an apostrophe, to denote thousands, and with a second one to denote millions. What simplifies writing on a flip chart also makes the report easier to read and can be learned quickly, even though it is not yet standard everywhere.
No further settings or parameters are required. Percentages are excluded from the display regardless of how large they are. The display has no impact on the calculations: DeltaMaster always calculates with the exact values, including decimal places. The figures are exported to Microsoft Excel as numbers written out in full to their original degree of precision, regardless of how they were displayed in DeltaMaster. Earlier versions of DeltaMaster do not support this formatting, but are not affected by the setting – the numbers are written out in full, as before. In planning applications, enter the numbers in full, not in compact notation.
To activate the display, switch to Modification mode.
Compact display as default in the application
As default for all reports in the current application, activate the display in the options (via the gear wheel 
in the menu bar), on the Presentation tab. In applications that you are creating with DeltaMaster 6.1.6 or higher, this is already the default setting.
Compact display in Graphical Tables
In addition, when you edit, you can set the display of numbers for Graphical Tables individually. You can find the options in the Edit menu (on the right) in the Presentation section. In this way for example, you can set the decision-oriented compact notation as the default and produce some “lookup tables” with accounting precision for other purposes. If none of the three options are selected, the general setting in the application options is applied.
An abbreviated display of numbers can also be attained with the following two methods. They are both based on the analysis model, one on measures and the other on (calculated) dimension members.
Formatting and scaling of measures
Output of values in thousands or millions can be attained in two ways: via a Custom .NET formatting string or via Scaling.
- Formatting strings come from IT. They describe how a numerical value is to be output as text. For example, they can be used to control which characters are to be used as thousands separators and decimal separators, how many decimal places are to be taken into account, and how negative numbers are to be displayed. This implicitly also means that it is possible to convert numbers to thousands or millions.
The formatting string can either be stated as a constant expression (in the Custom field) or determined dynamically by means of an MDX expression, for example in accordance with hierarchy levels. The link next to the input field for custom formatting opens a Microsoft website that explains the structure of formatting strings (see also DeltaMaster clicks! 02/2008, with an example of filter-dependent formatting with MDX).
- Scaling is known in the business world as well, and relates to the numerical values themselves: They are converted (divided) and thus transformed. Here too, DeltaMaster calculates internally with the original values. Besides powers of ten, other factors can also be stated here as factors, for example in order to convert units of measurement. In addition, the factor can be calculated by means of an MDX expression. This must return a numerical value, for instance from a reference to the selected member in a specific dimension (for example currency, unit, value view) or in accordance with hierarchy levels.
Formatting and scaling work in parallel: Scaling transforms values, and formatting controls their notation. Scaling is easy to use, but additionally requires formatting (in case of doubt: Number with 0 decimal places). Formatting strings are not exactly intuitive, but if you understand how they work, you can often avoid the need for additional scaling.
The concise notation in K, M, and B set out above can also be achieved with formatting strings:
| #,0, “K” | Scaling in thousands, no decimal places |
| #,0,, “M” | Scaling in millions, no decimal places |
| #,0,,.0 “M” | Scaling in millions, one decimal place |
The comma between the hash and the zero means that group separators (thousands separators) will be output if required; in English, this is usually a comma. The commas after the zero result in the scaling – for each comma, the value is divided by 1,000.
The settings on the Format tab, including MDX expressions, can be saved as a format template for number formats and reused with other measures (I want to menu top right on the tab).
You can access the measure properties in DeltaMaster 6 when Modeling. If the key figure is displayed in the Filter bar, click on it once to edit the properties. Otherwise, click on the three dots after the measures to open the Measure Browser; then edit the properties via the context menu.
Formatting with calculated members
This case too is described in detail in DeltaMaster clicks! 02/2014. Formatting and scaling can be linked to (calculated) members as well as to measures. If applicable, an auxiliary dimension is often provided in the analysis model for this, for example unit or value view. In this way, a specific formatting can be applied to multiple measures, eliminating the need to format them separately. In addition, one measure can be combined with various members for formatting instead of deriving multiple measures with differing formatting from one basic measure.
One special feature is that the formatting members can also be set explicitly in Presentation mode – this is not possible with formatted measures, and neither possible nor necessary with the automatic compact notation.
Calculated members can be edited in DeltaMaster 6 when Modeling. In the Filter bar, click on the name of a dimension to open the Dimension Browser. It is possible to add and edit calculated members via the context menu and the I want to menu. The formatting options correspond to those of the measures, but an MDX expression is not possible here. Scaling is achieved via a calculation using an MDX expression in the Definition field.
The two structure-driven approaches (via measures and calculated members) are tried and tested and an efficient tool for special tasks, usually in larger applications. The automatic compact display is universal and easier, as well as being more effective by virtue of the clever, value-dependent scaling.
Compact in tiles and Sparklines
In the case of two special displays, DeltaMaster uses compact notations as a matter of course:
In zoomed Sparklines, where space is particularly tight, the columns or dots are labeled in a particularly readable manner thanks to the apostrophe short-form.
And on application and report tiles as well, DeltaMaster automatically formats large, absolute numbers in a compact manner. Here, K represents thousands, M millions, and Bn billions.
