Remove Scientific notation in measure

[marcos_osorio]

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.

[Arul · Accepted solution]

Hi @marcos_osorio ,

Can you please check the datatype and let me know.

Thanks & regards,

-Arul

[v-yulgu-msft · Accepted solution]

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

[Anonymous · Accepted solution]

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. 

[gbhandari1s]

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.

[Zhangxp]

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

Thanks.

Grace

[Kris2x5 · Accepted solution]

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 · Accepted solution]

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 · Accepted solution]

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

[SKJ]

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

[Arul · Accepted solution]

Hi @marcos_osorio ,

Can you please check the datatype and let me know.

Thanks & regards,

-Arul

[marcos_osorio]

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!