Dynamic number format

[AhmadBakr]

Hi,

I have a measure [PO_Amount] which migh show wide range of numbers from thousands to billions. I chose dynamic format for it and entered the below in the "Format" formula bar:

VAR kil = 10^3

VAR mil = 10^6

VAR bil = 10^9

RETURN

SWITCH(

    TRUE(),

    [PO_Amount] >= bil, FORMAT([PO_Amount]/bil, "0.00B"),

    [PO_Amount] >= mil, FORMAT([PO_Amount]/mil, "0.00M"),

    [PO_Amount] >= kil, FORMAT([PO_Amount]/kil, "0.00K"),

    FORMAT([PO_Amount], "0.00")

)

To my surprize, it does not show the decimal point at all, e.g. if the value is 1,123,456,789 and should show as 1.12B, it appears 112B

[OwenAuger · Accepted solution]

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?

[OwenAuger · Accepted solution]

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?

[OwenAuger · Accepted solution]

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?

[AhmadBakr]

Hi @OwenAuger 

Thank you for your response.

In fact I tried something similar and did not work.

I tried the code you suggested and for a value 17.46bn (as auto formatted), it returned 0.00bnB (!)
I am not sure what might be missing.

[OwenAuger · Accepted solution]

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]

@OwenAuger 

Thanks a lot, this solved it. I was tuning all these (code plus format) but your code together with the right format setting did the job.

Much appreciated, you save me double the number of measures to write a dedicated format measure for every numerical measure 🙂

Thank you.

[AhmadBakr]

@OwenAuger 

Is there a way to reference a generic measure name, so I can assign the dynamic format code to a measure and use this measure name in the dynamic format string of any measure without the need to change the name of the measure to be formatted (highlighted red below)?

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

[OwenAuger · Accepted solution]

@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 🙂

[AhmadBakr]

The first solution appeals to me more and worked. you are right, when I assigned the format code to a separate measure and used it to format the Amt measure, didn't work, althought I tried (for the fun of it) to use VALUE() but no! May be later we can get some function like EXCEL's INDIRECT() 🙂

And thank you for the article as well. This is super useful.

Thanks a lot for your responses and support.