There are a few different ways of formatting numbers with Power BI, but one way that is really useful is to use dynamic format strings.
This is especially useful if you have numbers that cover a range (thousands, millions etc) and may also have different kinds of values, such as dollars, numbers, and percentages, within the one chart that you want to switch between.
We can use SWITCH to go between the different types of formatting, and we can also detect the magnitude of the number and therefore format the value according to its’ size also.
The number is formatted according to the size by first translating it into an absolute value using ABS and then passing a different format to the dynamic format string depending upon the size that is returned.
I’ve included an example of this used in the PBI report shown in the gif below.
And all of this is done while using dynamic format strings, so the value in the chart is still an actual number rather than a text value which is what happens if we use the FORMAT function within the measure itself.
The code I used in the format string is below:
DAX Dynamic Format String
///// format numbers dynamically depending on measure selection & amount
VAR __value = ABS ( SELECTEDMEASURE () )
VAR __format_number =
SWITCH(
TRUE(),
__value >= 10000000, ” #,#0,,.0M; (#,#0,,.0M)”, // if > 10M
__value >= 1000000, ” #,#0,,.00M; (#,#0,,.00M)”, // if > 1M
__value >= 10000, ” #,#0,.K; (#,#0,.K)”, // if > 10K
__value >= 1000, ” #,#0,.0K; (#,#0,.0K)”, // if > 1K
” #,0; (#,0)” // otherwise
)
VAR __format_dollar =
SWITCH(
TRUE(),
__value >= 10000000, ” $#,#0,,.0M; ($#,#0,,.0M)”, // if > 10M
__value >= 1000000, ” $#,#0,,.00M; ($#,#0,,.00M)”, // if > 1M
__value >= 10000, ” $#,#0,.K; ($#,#0,.K)”, // if > 10K
__value >= 1000, ” $#,#0,.0K; ($#,#0,.0K)”, // if > 1K
” $#,0; ($#,0)” // otherwise
)
VAR __format_percent = “0%; (0%)”
VAR __selection = SELECTEDVALUE (Category[Category])
VAR __formatted_number =
SWITCH (
__selection,
“Volume” , __format_number,
“Revenue” , __format_dollar,
“Gross Profit %” , __format_percent
)
RETURN __formatted_number
/////////
If you’d like to know about how I got the data labels to appear above the x axis labels, see my earlier blog post here.
For a copy of the PBIX file used in the visual see my Github here.
For more about Dynamic format stings see SQLBI article on Dynamic format strings.