DAX Workout 001 - Dynamic Format Strings

[tweinzapfel]

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.

Provide your DAX measure in your forum post.

Workout 001.pbix (256.1 KB)

[DaveC]

Workout 001.pbix (260.7 KB)

[DavieJoe]

> 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 )

[BrianJ]

@tweinzapfel ,

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

[DavieJoe]

More than one way to

[KimC]

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”))

[tweinzapfel]

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

[Dharma]

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

[Hakeem]

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

[jplouw]

Wow, amazing solution Brian

[jplouw]

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
)

[Pavel.Adam]

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 )

[Jerome_Pomelos]

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

[kaDargo]

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)

[Yted]

Here is my entry for the DAX Workout 001 - Dynamic Format Strings

Image with solution below:-

[Theiren]

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
        )
)

[tweinzapfel]

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.

[Pavel.Adam]

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.

[jplouw]

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

[kaDargo]

I like that one also.