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
)

[vonrueden.alysson]

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