Dynamic number format

Hi AhmadBakr 

The dynamic format string expression should be written to return a format string, rather than returning the formatted value itself.

For example, the format string expression should return values such as these to format billions, millions or thousands:

"0,,,.00B"

"0,,.00M"

"0,.00K"

Here is a suggested rewritten version of the format string expression:

VAR kil = 10^3
VAR mil = 10^6
VAR bil = 10^9
VAR amount = [PO_Amount]
RETURN
    SWITCH(
        TRUE(),
        amount >= bil, "0,,,.00B",
        amount >= mil, "0,,.00M",
        amount >= kil, "0,.00K",
        "0.00"
    )

You could add "#," to the start of each string for digit grouping:

VAR kil = 10^3
VAR mil = 10^6
VAR bil = 10^9
VAR amount = [PO_Amount]
RETURN
    SWITCH(
        TRUE(),
        amount >= bil, "#,0,,,.00B",
        amount >= mil, "#,0,,.00M",
        amount >= kil, "#,0,.00K",
        "#,0.00"
    )

Does the above work for you?

Ah right, with the Card visual (and certain other situations) make sure the visual's number format settings are not conflicting with the measure's number format.

For a Card, set

• Callout value > Display units = None
• Callout value > Value decimal places = Auto

Does that fix it?

AhmadBakr 

Good question 🙂

If a format string depends on a measure's value (as it does in your example), then I don't believe it's possible to assign the format string expression to a separate measure and then reference this measure within format string expressions of individual measures.

The function SELECTEDMEASURE() can be used to generically evaluate the "current" measure, but it only works when used directly in a format string expression, not in a measure referenced by a format string expression.

So the options I can suggest for applying this same format string to multiple measures are:

1. In your above expression, change [Amt] to SELECTEDMEASURE() , and replicate the dynamic format string code for every measure. This can be done relatively quickly with Tabular Editor (by selecting multiple measures and changing the dynamic format string expression property).
2. Rather than using measure dynamic format strings, instead create a calculation group containing a calculation item with the appropriate format string expression using SELECTEDMEASURE(). This calculation item could then be applied as a filter to control the formatting of any number of measures at visual/page/report level.
   See this article for example.

Hopefully that is of some help 🙂