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)
6 Likes
DaveC
March 17, 2023, 12:23pm
#2
2 Likes
BrianJ
March 17, 2023, 6:00pm
#4
@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
5 Likes
KimC
March 18, 2023, 9:00am
#6
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”))
4 Likes
@DavieJoe - I didn’t even think of using a log function here. But definitely a way to do that.
3 Likes
Dharma
March 21, 2023, 12:31pm
#9
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
1 Like
Hakeem
March 21, 2023, 1:11pm
#10
Here is my solution to the first DAX challenge.
DAX_Workout_001.pdf (111.7 KB)
jplouw
March 21, 2023, 5:02pm
#11
Wow, amazing solution Brian
1 Like
jplouw
March 21, 2023, 5:14pm
#12
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
)
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 )
1 Like
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
2 Likes
kaDargo
March 21, 2023, 11:15pm
#15
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
March 22, 2023, 3:41am
#16
Here is my entry for the DAX Workout 001 - Dynamic Format Strings
Image with solution below:-