topic Re: Need Help in number formating by using DAX commands in DAX Commands and Tips

Need Help in number formating by using DAX commands

Anonymous — Fri, 27 May 2022 05:10:13 GMT

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

Re: Need Help in number formating by using DAX commands

amitchandak — Fri, 27 May 2022 08:18:20 GMT

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

Re: Need Help in number formating by using DAX commands

Anonymous — Sat, 28 May 2022 13:57:37 GMT

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

Re: Need Help in number formating by using DAX commands

Whitewater100 — Sat, 28 May 2022 20:41:40 GMT

Hi:

You can format :

To Format to $1.56B

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

Re: Need Help in number formating by using DAX commands

Whitewater100 — Sun, 29 May 2022 01:12:19 GMT

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

Re: Need Help in number formating by using DAX commands

v-yanjiang-msft — Wed, 01 Jun 2022 08:59:30 GMT

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.