Reply
Highlighted
Member
Posts: 65
Registered: ‎08-07-2018
Accepted Solution

Substract measure from another measure: result should show 0,0 instead of -4,0E-8

Hello,

 

I have created a measure 'Delta' to show the difference between 2 other measures: 'Actuals' - 'Budget' (DeltaLEMTPFY Millions = [LE Millions] - [MTP FY Millions] ) . When the result comes close to 0, the result shows -4,0E-8. However it should show 0,0 (always 1 decimal)

In the matrix visual that I see I have set the decimals to 1:

 

decimal.JPG

 

Any idea how to solve this?

 

Thank you in advance!

 

 


Accepted Solutions
Senior Member
Posts: 317
Registered: ‎12-04-2017

Re: Substract measure from another measure: result should show 0,0 instead of -4,0E-8

You can round using the below DAX for a calculated column

 

Column = ROUND(Table2[Value], 1) 

Difference before and after rounding off is shown in screenshot below

va.PNG

First image values are not rounded off while the 2nd image values are rounded off. The Other scenario of negative values also handled 

 

 

View solution in original post


All Replies
Senior Member
Posts: 317
Registered: ‎12-04-2017

Re: Substract measure from another measure: result should show 0,0 instead of -4,0E-8

[ Edited ]

Hi @MartijnNL,

Power BI is showing the data as -4.0E-8 because the values for that case is -0.00000004.

 

So in this case you can round your values to the No. of decimals that you want. But again rounding off to 1 decimal will automatically show it as 0.0 and anyother negative value such as -5.2 will continue to show as -5.2

 

The Other aolution is that you should handle the negative values so that they are always replaced with 0. Once you do this the report will always show 0.0 whenever the value becomes negative. In this case a value of -5.2 will also be replaced with 0.0, which might not be desirable

Member
Posts: 65
Registered: ‎08-07-2018

Re: Substract measure from another measure: result should show 0,0 instead of -4,0E-8

Hi thanks, but I want to show negative values also. So, whether the value is positive of negative it should always show 1 decimal. So in this example I would expect -0,0 as end result. 

Senior Member
Posts: 317
Registered: ‎12-04-2017

Re: Substract measure from another measure: result should show 0,0 instead of -4,0E-8

So in this case you can round your values to the No. of decimals that you want. But again rounding off to 1 decimal will automatically show it as 0.0 and anyother negative value such as -5.2 will continue to show as -5.2

Member
Posts: 65
Registered: ‎08-07-2018

Re: Substract measure from another measure: result should show 0,0 instead of -4,0E-8

Do you know how to round the figure? Round function is not working (or I am using it incorrecly). Or do I need another function? 

 

Senior Member
Posts: 317
Registered: ‎12-04-2017

Re: Substract measure from another measure: result should show 0,0 instead of -4,0E-8

You can round using the below DAX for a calculated column

 

Column = ROUND(Table2[Value], 1) 

Difference before and after rounding off is shown in screenshot below

va.PNG

First image values are not rounded off while the 2nd image values are rounded off. The Other scenario of negative values also handled 

 

 

Member
Posts: 65
Registered: ‎08-07-2018

Re: Substract measure from another measure: result should show 0,0 instead of -4,0E-8

Thanks that works! I made a mistake using the round function. Thanks for helping me!