Formatting or settings to be done to showcase negative values

[Anonymous]

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

[v-yulgu-msft]

Hi @Anonymous,

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

Best regards,

Yuliana Gu

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

Except these 2 values, rest seem ok.