Dynamic format string - format

powerbiexpert22​ 

here is the quick reference for you

#,##0                 Whole number with thousands separator
#,##0.00              Thousands separator and two decimals
0                     Whole number without grouping
0.0                   One required decimal
#.##                  Up to two optional decimals
#,##0,.0 "K"          Scale to thousands
#,##0,,.0 "M"         Scale to millions
#,##0,,,.0 "B"        Scale to billions
0.0%                  Percentage with one decimal
#,##0;(#,##0);"-"     Positive; negative; zero
0.00 "units"          Add literal text

Hi, see if this helps.

For million example: #,0,,.00 M

# means shows a digit if needed that related to your measure after formatting

0 after #, means always show a digit here, minimal 0

The first comma (,) after # means use thousand separators

The 2 commas (,,) after #,0  is for scalling ie. 1 comma = divide by 1000, 2 commas = divide by 1000000

.00 after #,0,, is to show 2 decimal points

M at the end just to append the string M that correlated to division by 1000000 due to scaling

Examples:

so for 12 500 000
1- 12 500 000 / 1 000 000 = 12.5
2- use 2 decimal points = 12.50
3- add M = 12.50M
result > 12.50 M (thousand separator not used)


for 1 112 500 000
1- 1 112 500 000 / 1 000 000 = 1112.5
2- use 2 decimal points = 1112.50
3- add M = 1112.50 M
4- use thousand separator = 1,112.50 M
results > 1,112.50 M (thousand separator is used)


Here is a good example that you can try:
https://www.sqlbi.com/articles/introducing-dynamic-format-strings-for-dax-measures/

Here is where you can see example for more format options (not just "#,0.00 K" etc):
https://learn.microsoft.com/en-us/dax/format-function-dax

Hi powerbiexpert22​,

The easiest way to remember it: 0 means "always show a digit here", # means "show a digit only if needed", a comma between digits turns on the thousands separator, and each comma at the end (right before the decimal point or the end) divides the number by 1,000.

Format string 1234567.891 shows as Why ---------------- --------------------- ------------------------------- 0 1234568 whole number #,0 1,234,568 thousands separator #,0.00 1,234,567.89 2 decimals, always shown #,0.## 1,234,567.89 up to 2 decimals (hides trailing 0) #,0,"K" 1,235K one trailing comma = / 1,000 #,0.0,,"M" 1.2M two trailing commas = / 1,000,000 0.0% (0.256 -> 25.6%) % multiplies by 100 #,0;(#,0);"-" positive;negative;zero sections

So for K/M the only thing that changes is how many commas you put at the end, and the text in quotes is just a label. In a dynamic format string you pick the right one based on the size of the value:

-- Measure > Format: Dynamic, then use this as the format string expression
VAR v = ABS ( SELECTEDMEASURE () )
RETURN
    SWITCH (
        TRUE (),
        v >= 1000000000, "#,0.0,,,""B""",
        v >= 1000000,    "#,0.0,,""M""",
        v >= 1000,       "#,0.0,""K""",
        "#,0"
    )

Note that a quote inside a DAX string is written as two quotes (""M"" gives "M" in the format string), and that the format only changes how the value is displayed; the underlying number stays the same for sorting and totals.

So my overall approach would be:

0 = forced digit, # = optional digit -> comma between digits = separator, trailing comma = / 1,000 -> "K"/"M" are just quoted labels -> SWITCH on SELECTEDMEASURE() in the dynamic format string

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.