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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
marcos_osorio
Helper II
Helper II

Remove Scientific notation in measure

Hi, i have a measure that substracs two other measures. The database is in SQL, but the problem is that in a column instead of appear 0 it appears in scientific notation, like 3e-11, i tried ROUND, TRUNC for the measure but nothing seems to work.

How can I solve this?

 

Thank you very much.

4 ACCEPTED SOLUTIONS
Arul
Super User
Super User

Hi @marcos_osorio ,

Can you please check the datatype and let me know.

 

Thanks & regards,

-Arul

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


LinkedIn


View solution in original post

v-yulgu-msft
Employee
Employee

Hi @marcos_osorio ,

 

After loading data into desktop, please change the data type to Decimal, and set decimal places to 11 or above if you want to display the whole value. 

1.PNG

 

If you only want to round the value as a integer, set decimal place to 0.

2.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Anonymous
Not applicable

Did you ever find a solution? I am struggling with the same problem. It works when I change the fomar to currency but I would like to keep the decimal and just round it. 

View solution in original post

Kris2x5
Advocate II
Advocate II

I found an alternative solution that fixed the issue for me. You can define a custom format in the Modeling view as shown here: 
https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-custom-format-strings

Go to the Modeling view, select the field or measure you need to format, navigate to the Properties pane, pick "Custom" format from the dropdown, and then enter your desired format. 
I used a custom format of:
#,0.0;-#,0.0;0

The string is broken into three parts by each semicolon, defining the format for a positive number, negative number, and zero. In the above format I wanted commas to show and only one decimal place. If you wanted your negative number to be in parentheses, you could instead use #,0.0;(#,0.0);0

View solution in original post

8 REPLIES 8
gbhandari1s
New Member

I am having a similar problem. The below picture shows that it is being read as I want to> I set it up as text:

 

gbhandari1s_0-1661185928584.png

 

when i refresh this is what i get and it messes my reports:

gbhandari1s_1-1661186068372.png

 

This is an order id column and i i need to get distinct count but with this I always get 1.

 

I would appreciate any help in regards to this.

 

I encounted the same issue. Have you found the solution?

Thanks.

 

Grace

Kris2x5
Advocate II
Advocate II

I found an alternative solution that fixed the issue for me. You can define a custom format in the Modeling view as shown here: 
https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-custom-format-strings

Go to the Modeling view, select the field or measure you need to format, navigate to the Properties pane, pick "Custom" format from the dropdown, and then enter your desired format. 
I used a custom format of:
#,0.0;-#,0.0;0

The string is broken into three parts by each semicolon, defining the format for a positive number, negative number, and zero. In the above format I wanted commas to show and only one decimal place. If you wanted your negative number to be in parentheses, you could instead use #,0.0;(#,0.0);0

Anonymous
Not applicable

Did you ever find a solution? I am struggling with the same problem. It works when I change the fomar to currency but I would like to keep the decimal and just round it. 

v-yulgu-msft
Employee
Employee

Hi @marcos_osorio ,

 

After loading data into desktop, please change the data type to Decimal, and set decimal places to 11 or above if you want to display the whole value. 

1.PNG

 

If you only want to round the value as a integer, set decimal place to 0.

2.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Please check for Data Display unit as well. It will remain scientific if Display unit is not selected Auto...

Arul
Super User
Super User

Hi @marcos_osorio ,

Can you please check the datatype and let me know.

 

Thanks & regards,

-Arul

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


LinkedIn


Now it's decimal. 

If i change it to currency the problem appears to solve, but i would like to keep is as decimal.

Thank you very much!

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors