Need Help in number formating by using DAX commands

[Anonymous]

Hi ALL,
I need some help in number formatting in DAX , Below is the scenario & the DAX function which I have written.

I have revenue field in my source and as Power Bi represents Billion as "bn" and my client wants it to represent it as "B" , so they want if the numbers are less than 1000 then it represent at $ , if it is less than 1000000 then it should represent as $K , if it less than 1000000000 then it should be representing as $M and anything greater than that would be termed as $B. 

Here is what i have written :
"$"&
VAR number_ =
MAX ([Total Revenue] )
VAR decimal = "0.0"
RETURN
IF (
number_ >= 1000000000,
FORMAT ( number_ / 1000000000, decimal & "B" ),
IF ( number_ >= 1000000, FORMAT (number_ / 1000000, decimal & "M" ) )
).
when I create a new column and write a above dax function it is treating as Text and i am not able to summarize it , 
if I create a new measure then I input the above DAX function then it is not even allowing me to change the format from Text to Decimal.
Kindly Please help!!
Thanks & Regards
Shiv

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ,

According to your description, by my test, a calculated column can't get the correct total anyway, but a measure can, because measure can first get the total number then format it.

I create a sample.

Here's my solution, create a measure, use SUM in the formula, not MAX.

Measure =
VAR number_ =
    SUM ( [Total Revenue] )
VAR decimal = "0.000"
RETURN
    IF (
        number_ >= 1000000000,
        FORMAT ( number_ / 1000000000, decimal & "B" ),
        IF ( number_ >= 1000000, FORMAT ( number_ / 1000000, decimal & "M" ) )
    )

 Get the correct result. In order for measure to get multiple values, additional columns are required in the visual.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Whitewater100]

Hi:

If you new to format text to number. In DAX usually INT or VALUES function can helpful. I think you can use a SWITCH statement. I did a little reseach on how to Format like you mentioned and found the following:

To Format to $1.56B

Measure = format(1558753918,"$#,,,.##B")  the number can be substituted for your measure. I have other examples below.  I hope this helps!

"Currency (2)", FORMAT (             1234.567, """US$"" #,0.00" )

Currency (2)

US$ 1,234.57

Millions:

Sales to (M) = FORMAT([Sales Amount], "#,##,,.0M")     = 2,715.3M

Sales to (M) = FORMAT([Sales Amount], "#,##,,M")    =2,715M

Europe:

FORMAT(MuMeasure[EU], "#,##,,.0M") 

Thousands

Sales to (K) = FORMAT([Sales Amount], "#,##,K")   =205K

Sales to (K) = FORMAT([Sales Amount], "#,##,.0K") put it to one decimal   294.8k vs 294801

[Whitewater100]

Hi:

You can format :

To Format to $1.56B

Measure = format(1558753918,"$#,,,.##B")    * place measure instead of 1558753918.

[amitchandak]

@Anonymous , first create a measure and do that formatting there. It will text measure but will work

[Anonymous]

Hi @amitchandak ,
I have followed the way you mentioned but when i create a bar chart with the measure , it is not giving me any data as it is text field.
Thanks & Regards
Shiv Kumar