# DAX Workout 001 - Dynamic Format Strings

Welcome to Workout #1.

For this workout, you are asked to create a DAX Measure that formats the dollar amounts in the left column to look like the right colum. Overall, when the amount is in the thousands or greater, display the number with 1 decimal place and show the corresponding letter.

(Note - the USD \$ sign is provided, but feel free to update this to either leave off or include a difference currency symbol).

A PBI file is provided with a sample dataset or you may simply create your own.

Workout 001.pbix (256.1 KB)

8 Likes

Workout 001.pbix (260.7 KB)

3 Likes ``````> Formatted Result =
> VAR _Dollars = [Dollar Total]
> VAR _CurrentNumber =
>     SELECTEDVALUE ( 'Table'[Dollar Amount] )
> VAR _LogRatio =
>     IF ( _CurrentNumber > 0, DIVIDE ( LOG ( _CurrentNumber ), LOG ( 1000 ) ) )
> VAR _FinalFormat =
>     ROUNDDOWN ( DIVIDE ( LOG10 ( _CurrentNumber ), 3 ), 0 )
> VAR _String =
>     SWITCH (
>         _FinalFormat,
>         0, "\$#,0",
>         1, "\$#,0,.0#K",
>         2, "\$#,,.00#M",
>         3, "\$#,0,,,.0#B"
>     )
> RETURN
>     FORMAT ( _CurrentNumber, _String )
``````
9 Likes

Great problem. Actually made me have to go back and review custom format string rules.

My DAX measure (click Summary to expand):

Summary
``````
Formatted Result =

// Uses alternate parameter to prevent card from showing blank
// if no selection is made in table
VAR _SelVal = SELECTEDVALUE( Data[Dollar Amount], -9999)

VAR _Number =
SWITCH( TRUE(),
_SelVal < 1000, _SelVal,
_SelVal < 1000000, DIVIDE( _SelVal, 1000),
_SelVal < 1000000000, DIVIDE( _SelVal, 1000000),
DIVIDE( _SelVal, 1000000000)
)

VAR _NumFormat =
SWITCH( TRUE(),
_SelVal < 1000, "\$##0",
_SelVal < 1000000, "\$##0.0," & "k",
_SelVal < 1000000000, "\$##0.00,," & "M",
"\$##0.0,,," & "B"
)

VAR Result =
IF( _SelVal = -9999,
"Value not selected",
FORMAT( _Number, _NumFormat )
)

RETURN Result
``````

8 Likes

More than one way to  4 Likes

Hi Tim,

Thanks for the challenge, it was a surprise to see it today.

Here is my solution:

Format Dollar Amount =

VAR MeasureFormat=sum(Data[Dollar Amount])

RETURN

SWITCH(TRUE(),

MeasureFormat>=10000000,FORMAT(MeasureFormat,“\$#,#,.0”)&“B”,

MeasureFormat>1000000,FORMAT(MeasureFormat,“\$#,##,.00”)&“M”,

MeasureFormat>100000,FORMAT(MeasureFormat,“\$#,###,.0”)&“k”,

FORMAT(MeasureFormat,“\$###,0”)) 8 Likes

@DavieJoe - I didn’t even think of using a log function here. But definitely a way to do that.

4 Likes

Here is my Submission for DAXworkout001 - Dynamic Format String

@tweinzapfel

// DAX Measure below //
Formatted Number =

VAR _TotalNumber = SUM(Data[Dollar Amount])

VAR _Formattednumber =
SWITCH(TRUE(),
_TotalNumber <= 999 , FORMAT(_TotalNumber, “\$#”),
_TotalNumber <= 999999 , FORMAT(_TotalNumber, “\$#,.0”&“K”),
_TotalNumber <= 9999999 , FORMAT(_TotalNumber, “\$#,.00”&“M”),
FORMAT(_TotalNumber, “\$#,.0”&“B”))

VAR _Filterhandle = IF(HASONEFILTER(Data[Dollar Amount]), _Formattednumber, BLANK())

Return
_Filterhandle

2 Likes

Here is my solution to the first DAX challenge.
DAX_Workout_001.pdf (111.7 KB)

2 Likes

Wow, amazing solution Brian

2 Likes

Solution =
//Solution for workout 1

VAR SelectedDollar = SELECTEDVALUE(Data[Dollar Amount])

RETURN
SWITCH
(
TRUE(),
SelectedDollar < 999, FORMAT(SelectedDollar,“\$#,0” ), – Less than 1K
SelectedDollar <= 999999, FORMAT(SelectedDollar, “\$#,0,.0#K” ), – Less than 1M
SelectedDollar <= 999999999, FORMAT(SelectedDollar,“\$#,0,.00#M” ), – Less than 1B
SelectedDollar <= 999999999999, FORMAT(SelectedDollar, “\$#,0,.0#B”) – Less than 1T
)

1 Like

I hate if/switch conditions. I prefer math approach - if it exists. Here is a math based approach, extended also to “T” suffix (trillions). No safeguarding if number is bigger than Trillion. Enjoy.

Amount Text =
VAR suffixVec = " kMBT"
VAR actAmount =
SUM ( input[Dollar Amount] )
VAR actThousandToThePowerOf =
QUOTIENT ( ROUNDDOWN ( LOG ( actAmount, 10 ), 0 ), 3 )
VAR outNumber =
DIVIDE ( actAmount, POWER ( 1000, actThousandToThePowerOf ) )
VAR outFormat =
“\$”
& IF (
actThousandToThePowerOf = 0,
“#”,
“#.0”
& MID ( suffixVec, actThousandToThePowerOf + 1, 1 )
)
RETURN
FORMAT ( outNumber, outFormat )

6 Likes

Hi all, here is my entry for the DAX challenge:
Formatted Result =
var amount = SUM(Data[Dollar Amount])

var formatted =
SWITCH(true,
amount>1000000000, FORMAT(amount,“\$ 0,.#B”),
amount>1000000, FORMAT(amount,“\$ 0,.#M”),
amount>1000, FORMAT(amount,“\$0,.0k”),
FORMAT(amount,“\$0”)
)

RETURN formatted

Thanks

4 Likes

Here is my solution.

Formatted Result =
VAR x = SUM(Data[Dollar Amount])
VAR intLog = INT(LOG(x))
VAR formatString =
SWITCH
(
TRUE(),
intLog >= 9,“\$#,0,.0#B”,
intLog >= 6,“\$#,0,.00#M”,
intLog >= 3,“\$#,0,.0#k”,
“\$#,0”
)

Return FORMAT(x,formatString)

4 Likes

Here is my entry for the DAX Workout 001 - Dynamic Format Strings Image with solution below:-

4 Likes

Here is my measure

``````Formatted Result =
VAR _formatB = FORMAT(MAX(Data[Dollar Amount]), "\$0,,,.0B;\$0,,,.0B-")
VAR _formatM = FORMAT(MAX(Data[Dollar Amount]), "\$0,,.0M;\$0,,.0M-")
VAR _formatK = FORMAT(MAX(Data[Dollar Amount]), "\$0,.0K;\$0,.0K-")
VAR _formatN = FORMAT(MAX(Data[Dollar Amount]), "\$0;\$0-")
VAR _amount = MAX(Data[Dollar Amount])

RETURN
IF(
_amount >=0,
SWITCH(TRUE(),
_amount/1000 < 1, _formatN,
_amount/1000 < 1000, _formatK,
_amount/1000 < 1000000, _formatM,
_amount/1000 >= 1000000, _formatB
),
SWITCH(TRUE(),
_amount/-1000 < 1, _formatN,
_amount/-1000 < 1000, _formatK,
_amount/-1000 < 1000000, _formatM,
_amount/-1000 >= 1000000, _formatB
)
)
``````
1 Like

These are great variations. One approach that I didn’t think of which I find very unique is @Pavel.Adam use of having the letters stored in a variable and then using the MID() text function to use the log value from a positioning standpoint.

3 Likes

Well, I use a data catalog based table that (with other attributes) specifies what measure should be formatted how: rounding, decimals, units. Doing so, customer is able to define its own requirements to each KPI - without me or any other DAX developer to deal with DAX formatting. Using such a table, I define formatting option in Power Query - it is much faster to retrieve short string than to create such a string in DAX measure again and again.

3 Likes

Once again- the importance of fundamental maths and stats for Analyst are showcased

4 Likes

I like that one also.

1 Like