Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Need Help with Dax DAX commands in number formating

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

4 REPLIES 4
Whitewater100
Solution Sage
Solution Sage

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
Not applicable

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

 

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:

 

Whitewater100_0-1653500258887.png

 

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

 

Anonymous
Not applicable

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

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.