cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vishy86 Helper V
Helper V

Formatting or settings to be done to showcase negative values

Hi,

 

I have a measure which is a sum of 2 columns:

Measure = SUM(Column A) + SUM(Column B)

PS: This is not a currency value.

 

When I use the measure in a Table visualization and see its values across a dimention, I see that there are a couple of places where the values appear as 1E-12 and -2E-13. However, when I export the results to excel, these values appear as 0 but on clicking on the cell, I see 1.30739863379858E-12 and -2.27373675443232E-13 on the fomula bar in excel.

 

Is there any formatting I need to do in Power BI to show the negative values as they are in a calculation?

If the addition results in a negative value, lets's say -1.307, I should see the same value in the report.

 

Regards,

Vishy

4 REPLIES 4
vishy86 Helper V
Helper V

Re: Formatting or settings to be done to showcase negative values

Just to clarify, I am not sure those values 1E-12 and -2E-13 are negative. 

 

Except these 2 values, rest seem ok.

 

 

Microsoft v-yulgu-msft
Microsoft

Re: Formatting or settings to be done to showcase negative values

Hi @vishy86,

 

Please set the format for that measure to "Scientific" under Modeling tab.

1.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.
vishy86 Helper V
Helper V

Re: Formatting or settings to be done to showcase negative values

Hi Yuliana,

 

I do not want the numbers to appear in scientific format. If the addition results in a value which is similar to the one in your test, it should display 0 as I have set the formatting to round off and not show decimal points. Format is Decimal Number.

 

Except the 2 rows, rest all show 0 in my Power BI report.

 

I checked SQL as well and queried for the same, the result is 0 as both the columns have value 0.

 

Please let me know if there is any formatting I am missing. I need the value to show as 0 and not in scientific format.

 

Regards,

Vishy

vishy86 Helper V
Helper V

Re: Formatting or settings to be done to showcase negative values

Hi,

 

I used the ROUND() DAX function and it resolved the issue i.e. ROUND(SUM,0). It got rid of the numbers displated in scientific format.

 

Not sure, why the Format -> Field Formatting -> Value decimal places 0 did not work.

Same setting was checked in Modeling tab as well and Format was Decimal Number with 0 decimal places.

 

Any tips or suggestions on the above would be helpful. Thanks.

 

Regards,

Vishy

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors