Need Help with Dax DAX commands in number formating

[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

[Whitewater100]

Hi:

Two functions change explicit type conversions.

VALUE and INT

Below are some practical examples of usinf format:

I hope this helps!

FORMAT EXAMPLES:

Top few measures go  with bottom examples:

formatPercent = "#,0.0%;-#,0.0%;#,0.0%"

formatUSD = "\$#,0;(\$#,0);\$#,0"

formatWholeNumber = "#,0"

FORMAT ( number_ / 1000000000, ),"#,0.0")

OKPIS This Year = SWITCH( SELECTEDVALUE( 'OKPIS Labels'[Order] ),

    1, FORMAT([# Items Sold],"#,##0 ;($#,##0)"),

    2,  FORMAT([# Accounts Sold],"#,##0 ;($#,##0)"),

    ,

       0

)

OKPIS Last Year = SWITCH( SELECTEDVALUE( 'OKPIS Labels'[Order] ),

    1, FORMAT([No Items LY],"#,##0 ;($#,##0)"),

    2,  FORMAT([No. Accounts LY],"#,##0 ;($#,##0)"),

       0

)

[Anonymous]

Hi @Whitewater100 ,
Thank you so much for your reply!!.
I am trying to address two issues here .

• Representing Billion as "B" from "bn".
• 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. 
  
  The above solution which you have given does not meet both the requirments , Hence can you please help me .
  Thanks & Regards
  Shiv Kumar

[Whitewater100]

Hello:

I will paste some examples below. I believe you will want to do some type of IF or SWITCH statement. I will paste example of that format below. I put a number measure below for demo purposes. [Cumu Current] is just a measure that results in a dollar figure.

Format Billion 5.25B

Format B = format([Cumu Current],"$#,,.##B")

Format 1000’s

Format B = format([Cumu Current],"$####,K")

490k   or2110k for 2,110,000

Format Million

Format B = format([Cumu Current],"$###,,.##M")

490,000 = .49M

Each # sign after ,. Is how many places – above ##M  = .49M  ###M = .490M

Example of statement to SWITCH:

I hope you have enough to solve now. Thanks...

[Anonymous]

Hi @Whitewater100 ,
Thanks for helping!!
But the above solution is still not working .
Below is how have created the page .
I have a company selection , so if i click on a company it shows the revenue in the card , and I have 1000 company's , and my data is at the most granular level.
Thanks & Regards
Shiv