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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Microsoft Employee
Microsoft 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
Microsoft Employee
Microsoft 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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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