1 Like
@DaveC How [Value1] and [Value2] is obtained. Can you explain?
VAR CalcTable =
TOPN(
1,
FILTER(
{
(DIVIDE(CurrentNum, 1), BLANK()),
(DIVIDE(CurrentNum, 1000), "K"),
(DIVIDE(CurrentNum, 1000000), "M"),
(DIVIDE(CurrentNum, 1000000000), "B")
},
[Value1] > 1
),
[Value1],
ASC
)
“Value1” and “Value2” are the names that are automatically assigned to the table that was created using the { } table constructor.
{
(DIVIDE(CurrentNum, 1), BLANK()),
(DIVIDE(CurrentNum, 1000), "K"),
(DIVIDE(CurrentNum, 1000000), "M"),
(DIVIDE(CurrentNum, 1000000000), "B")
}
creates a table with the result of the DIVIDE function in column “Value1” and the text strings containing the letters in column “Value2”.
Hope this makes sense…
1 Like
Ok, Excellent!
Hi There,
I completed this using the new Dynamic feature that was just released in Power BI. I’m late for the this workout.
Note: this option is in preview mode so becareful not to put in full production until Microsoft has full implelmented it.
Thanks for the workout
Keith
2 Likes
@Keith ,
Spot on perfect application of this new capability.
Great to have this in the thread for reference - thanks!
- Brian
2 Likes
thanks @BrianJ
I thought I would give it a try using the new function after seeing a youtube video done by Curbal today 
Here is the video reference
thanks Keith
3 Likes
FYI – Here’s a different way of implementing this using a helper table, so that you can control the format selection from a slicer.
– Brian
2 Likes
Bottom of the dropdown list is Dynamic
Here is my code:
Format Outcome =
if(
LEN(Data[Dollar Amount])<4,
FORMAT(Data[Dollar Amount],“£0”),
if(
(LEN(Data[Dollar Amount])>3&&
LEN(Data[Dollar Amount])<7),
FORMAT(Data[Dollar Amount],“£#,##0,.0 K”),
if(
(LEN(Data[Dollar Amount])>6&&
LEN(Data[Dollar Amount])<10),
FORMAT(Data[Dollar Amount],“£#,##0,.0 M”),
FORMAT(Data[Dollar Amount],“£#,##0,.0 B”)
)
)
)
Formatted =
var _formatted =
SWITCH(
TRUE(),
[Dollar Amount]>1000000000,FORMAT([Dollar Amount],“$#,.0B”),
[Dollar Amount]>1000000,FORMAT([Dollar Amount],“$#,.00M”),
[Dollar Amount]>100000,FORMAT([Dollar Amount],“$#,.0K”),
FORMAT([Dollar Amount],“$###”)
)
return _formatted
here is my solution
Formatted Result =
var amount =
SELECTEDVALUE(
Data[Dollar Amount]
)
var formatted =
SWITCH(
true(),
amount>1000000000, FORMAT(amount/1000000000,“$#.0B”),
amount>1000000, FORMAT(amount/1000000,“$#.00M”),
amount>1000, FORMAT(amount/1000,“$#.0K”),
FORMAT(amount,“$#”)
)
RETURN formatted
Here is my submission for Workout-001.
Its too late but good start for me 
@tweinzapfel really cool stuff, exploring each workout.
Workout 001.pbix (258.9 KB)
Here is my submission Better late than never 
Total Dollar Amount CF =
VAR __BaseMeasure = [Total Dollar Amount]
VAR __ResultCheck = LEN( __BaseMeasure )
VAR __Result =
SWITCH(
TRUE(),
__ResultCheck < 4, FORMAT( __BaseMeasure, "$0" ),
__ResultCheck <= 6, FORMAT( DIVIDE( __BaseMeasure, 1000 ), "$#,0.0k" ),
__ResultCheck <= 9, FORMAT( DIVIDE( __BaseMeasure, 1000000 ), "$#,0.0M" ),
__ResultCheck <= 12, FORMAT( DIVIDE( __BaseMeasure, 1000000000 ), "$#,0.0B" ),
FORMAT( __BaseMeasure, "$#,0.0" )
)
Return
__Result
I didn’t use a new feature because it is still in Preview so I won’t use it in any project anyway.
Workout 001.pbix (260.8 KB)
Hi All,
I have attempted thes solution in Both ; Regular aswell as Dynamic Formatting option.
Dax for Both solutions is same, just used slightly differently.
Dax for Solution1:
Click to See Details
Dax for Solution2:
Click to See Details
Here is my solution. My “M” is rounded to 1, so it shows a bit different than what the results should look like.
1 Like
I suppose you would not need division in dynamic format strings.
