Forum Discussion
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.
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.
If you only want to round the value as a integer, set decimal place to 0.
Best regards,
Yuliana Gu
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;0The 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
8 Replies
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;0The 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
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.
If you only want to round the value as a integer, set decimal place to 0.
Best regards,
Yuliana Gu
Please check for Data Display unit as well. It will remain scientific if Display unit is not selected Auto...
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!
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:
when i refresh this is what i get and it messes my reports:
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
