cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
MartijnNL Member
Member

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!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Thejeswar Senior Member
Senior Member

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

6 REPLIES 6
Thejeswar Senior Member
Senior Member

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

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

MartijnNL Member
Member

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. 

Thejeswar Senior Member
Senior Member

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

MartijnNL Member
Member

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? 

 

Thejeswar Senior Member
Senior Member

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

MartijnNL Member
Member

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!

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 117 members 1,421 guests
Please welcome our newest community members: