Compact Number Formatting for Data Size Measures: Kb, Mb, Gb

Dynamic format strings were introduced in Power BI in May 2023, eliminating the need for various workaround solutions when implementing flexible measure formatting. For instance, common tasks are adding currency symbols, measurement units or applying compact number formatting to measures. Previously, these tasks were handled using the FORMAT() function, calculation groups, or various tricks involving measure duplication.

Each of these methods had its drawbacks. For example, FORMAT() converts a numeric measure to a text data type, making it difficult for further calculations. Additionally, such measures cannot be used in visuals where numeric values are needed (such as graphs).

Compact Decimal Number Formatting

By now, numerous comprehensive articles are available online with examples of configuring Dynamic Format Strings. One classic example of reducing the number of digits displayed is described in the article by Marco Russo and Alberto Ferrari. The article provides the following code as an example of compact number formatting:

VAR CurrentValue = SELECTEDMEASURE()
RETURN
    SWITCH (
        TRUE (),
        // Special cases to force K and M in the sample report
        CurrentValue <= 1E3, "#,0.00",
        CurrentValue <= 1E6, "#,0,.00 K",
        CurrentValue <= 1E9, "#,0,,.00 M",
        CurrentValue <= 1E12, "#,0,,,.00 G"
    )        

It’s always a matter of preference, but if we want to store all formatting settings in a separate helper table or use custom formatting to present numbers in a compact form (see the next section), we’ll need to modify the DAX code.

First, let’s create a separate table with the required formats:

Here, Ordinal serves as the key in the formatting calculation measure, which we will use to retrieve the required format.

The key used to determine the desired order of magnitude is the exponent for the base 1000: 1000^1 = 1E3, 1000^2 = 1E6, 1000^3 = 1E9, etc. Thus, the code for generating the dynamic string format can be written as follows:

VAR _base = 1000
VAR _value = IF (SELECTEDMEASURE() > 0, SELECTEDMEASURE(), 1)
VAR _ordinal = TRUNC(LOG(_value, _base))
VAR _format = LOOKUPVALUE(Units[Decimal formatting], Units[Ordinal], _ordinal)
RETURN
    _format        

The result of the above measures is shown in the table below:

The measure Total cells (numeric formatting) contains the numeric value of the cell sum. Unlike its string representation, it can be used for further mathematical operations and applied in any visuals.

Compact Number Formatting for Bytes

The numeric formatting above works well for representing numbers in the decimal system. But what if we need to create a dynamic measure that displays values in bytes (b), kilobytes (Kb), megabytes (Mb), gigabytes (Gb), where the number base is not 1000 but 1024? Unfortunately, standard Excel-based formatting is tailored to the base-10 numeral system and won’t help us with data size representation.

As with the decimal system, the simplest solution ?can be to create a measure formatted as a string. For example:

Size (string formatting) = 
    VAR _value = SELECTEDVALUE('OLAP cubes'[Size, bytes ( basic formatting)]) 
RETURN 
    SWITCH (
        TRUE (),
        _value <= 1024, FORMAT(_value, "#0 b"),
        _value <= 1024^2, FORMAT(_value / 1024, "#0 Kb"),
        _value <= 1024^3, FORMAT(_value / 1024^2, "#0 Mb"),
        _value <= 1024^4, FORMAT(_value / 1024^3, "#0.0 Gb"),
        FORMAT(_value / 1024^4, "#0.00 Tb")
    )        

In this case, we still face the same limitations of string measures in Power BI.

But can the same dynamic format strings approach be used without modifying the original numeric measure? It turns out, yes!

To do this, we’ll add a few helper fields to the settings table, as shown in the picture:

The field Size denominator serves as the base for converting between number magnitudes and is calculated similarly to the decimal system: 1024^1, 1024^2, 1024^3, etc. Size denominator determines the desired number of decimal places to the right of the decimal point, while Size unit provides the appropriate units of measure for large values.

Now, let’s write the Format String Expression for formatting data size:

VAR _base = 1024
VAR _value = IF (SELECTEDMEASURE() > 0, SELECTEDMEASURE(), 1)
VAR _ordinal = TRUNC(LOG(_value, _base))

VAR _denominator = LOOKUPVALUE(Units[Size denominator], Units[Ordinal], _ordinal)
VAR _rounding = LOOKUPVALUE(Units[Size rounding], Units[Ordinal], _ordinal)
VAR _unit = LOOKUPVALUE(Units[Size unit], Units[Ordinal], _ordinal)

VAR _format = """" & ROUND((SELECTEDMEASURE() / _denominator), _rounding) & """" & _unit
RETURN
    _format        

The first calculation block is identical to the code for the base-10 number system. We retrieve the necessary parameters in the second block based on the calculated Ordinal. Finally, for the variable _format, we calculate the target numeric representation and convert this value into a string.

The double quotes in _format “escape” placeholder characters that might otherwise be interpreted as part of the format string syntax. Recall that the Dynamic format string uses specific syntax with predefined formatting templates. For instance, “#” and “0” refer to parts of the number, while “.” represents the decimal place. Wrapping the value in double quotation marks signals the DAX engine to interpret the value as a string. The number, calculated via ROUND((SELECTEDMEASURE() / denominator), is converted by the interpreter during concatenation with an empty string (“”).

The result of the String Expression calculation is shown in the screenshot below. Unlike Size (string formatting), the Size (numeric formatting) measure is a number formatted according to the calculated value in the Format String Expression. As you can see from the picture, the representations of the string and numeric measures appear identical, while the numeric measure remains unchanged and is available for use in any visuals.

Wrap Up

Dynamic Format Strings is a powerful feature. Its capabilities go beyond typical use cases described in the basic manual. With a bit of creativity, you can create entirely custom visualizations, such as “data bars” for fields that mimic text labels:

This is one of the visuals featured on the main page of my blog.

As usual, the samples described in this article are available on my GitHub.

Feel free to share in the comments the types of tasks you’ve tackled with Dynamic Format Strings.