Show .4 as 0.4 when converted to millions using custom dynamic format

[akhilduvvuru]

Team, I have amounts that should show in millions. If there is a negative value then should show in brackets. I'm using this custom dynamic logic to show the same.

"#,,.0;(#,,.0);#,,.0", "#,##0;(#,##0);#,##0"

This logic is working fine as expected for all the values except for the smaller amounts (mentioned in red below).
However,

1. For bigger amounts, the thousand comma seperator is not coming.

2. For smaller amounts, It should show as Expected Output field below.

Amount	Formated in Millions (using above format)	Expected Output
4,592,207,722	4592.2	4,592.2
371,820,100	371.8	371.8
90,000,000	90.0	90.0
-625	(.0)	(0) or (0.0)
575	.0	0 or 0.0
0	.0	0 or 0.0
304,665	.3	0.3

Someone please help me with the expected output.

Thanks!!

[v-heq-msft]

Hi @akhilduvvuru ,

Here some steps that I want to share, you can check them if they suitable for your requirement.

Here is my test data:

Create a measure

Measure = SELECTEDVALUE('Table'[Amount])/1000000

Then go to Measure Tools and set Format as dynamic and write this code

"#0.0;(#0.0);#0.0"

Final output

Best regards

Albert He

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

[akhilduvvuru]

Thanks @v-heq-msft for your response. This works as expected for one of the requirements! Can you also help me with the comma thousand seperator? Eg: 4592207722 --> 4,592.2.

Also, I have a requirement where I have a slicer with 2 values (1. Millions, 2. None). When I select "Millions", I should show the amount in Millions as I explained in my requirement above. When I select "None", I should be able show the actual amount.

As you suggested, If I do Amount/1000000, I'm not able to switch between Millions and actual amount.

Thanks!!

[v-heq-msft]

Hi @akhilduvvuru ,
I have modified the above steps and code according to your needs,
1.Create a calculate column

Column = VALUE('Table'[Amount])

2.Change the format of the column in Model view
Select the column and create a number format code in Custom format

#,#0,,.0;(##0,,.0);#,0.0

3.Create a filed parameter and rename Amount as Column as millions, Amount as None

4. Drag parameter as columns of table

5.Final output

Best regards

Albert He

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

[akhilduvvuru]

Thanks for this @v-heq-msft - Is there a way where we can do it without duplicating the field? Because I have many fields like with the same formatting requirement. I cannot duplicate the field.

Thanks!! 

[v-heq-msft · Accepted solution]

Hi @akhilduvvuru ,
I'm sorry I'm not quite sure what you mean by duplic filed. But as I understand it, you can use measure to avoid reusing this field in the model.
1.Create a measure

Measure = SELECTEDVALUE('Table'[Amount])

2.Set measure format

“#,#0,,.0;(##0,,.0);#,0.0”

3.Final output

Regarding the switching between the two views before and after the conversion, in addition to using the filed parameter as described above, you can also use bookmarks for this purpose. Here are the steps:
1.Create two blank buttons and set name as millions and none

2.Create duplicate page

3.Create two bookmarks and name them as miilions and none, in millions has all visiualizations and in none has only amount column

4.Apply these bookmarks to buttons

5.At this point you can use the buttons to switch between the different views.
Because measure cannot exist independently of the original column data, it must exist with the AMOUNT column on top of the view.

Best regards

Albert He

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

[amustafa]

To format values in millions and ensure that a value like .3 million is displayed as 0.3, you can use a custom format string in Power BI. The format you are looking for would be something like "0.0,,;(-0.0,,);0.0,,". This format will show the values in millions with one decimal place and add a leading zero for numbers less than 1.

[akhilduvvuru]

Hi @amustafa 

Thanks for your quick response! Unfortunalty both your format strings are not working as expected. Both are returning the actual numbers.