cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RGallagher320
Frequent Visitor

Total column not using formatted values

I have four columns. Let's call them Column A, B, C and D.  I am making a new column (E), that is A - (B+C+D).  I formatted ALL columns to Decimal number and 2 decimal numbers.  But for example, if A = 10.00 and B=0.00, C=10.00 and D=0.00, then 10.00-(0.00+10.00+0.00) = 0.00.  I am noticing though if I do a check on the total, it's sometimes not resulting to TRUE in my SWITCH statement. I am finding out that it's resulting in a very close to zero negative number (like -.0000000321).  So it appears that it's not using the 0.00 result in the SWITCH statement (it's using the close to zero negative number instead).  Any thoughts why it wouldn't use the two decimals if it's formatted that way? Thanks!

1 ACCEPTED SOLUTION
v-deddai1-msft
Community Support
Community Support

Hi @RGallagher320 ,

 

 You can also format the measure and the new column to Decimal number and 2 decimal numbers. When you format the column and measure, it will just change the displayed content does not change the true value of the underlying data, and the formula uses the underlying data

 

In your case, when you change column B and column D to Decimal number and 2 decimal numbers , the underlying value for them could be  such as 0.00123 and 0.00000001233113, and you limit them to 2 decimal numbers , so it will show like 0.00. 

 

Underlying data:

Capture.PNG

 

Result:

 

Capture1.PNG

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

View solution in original post

3 REPLIES 3
v-deddai1-msft
Community Support
Community Support

Hi @RGallagher320 ,

 

 You can also format the measure and the new column to Decimal number and 2 decimal numbers. When you format the column and measure, it will just change the displayed content does not change the true value of the underlying data, and the formula uses the underlying data

 

In your case, when you change column B and column D to Decimal number and 2 decimal numbers , the underlying value for them could be  such as 0.00123 and 0.00000001233113, and you limit them to 2 decimal numbers , so it will show like 0.00. 

 

Underlying data:

Capture.PNG

 

Result:

 

Capture1.PNG

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

View solution in original post

MFelix
Super User III
Super User III

hi @RGallagher320 ,

 

the decimals that you see is only visual so if you have more values on the number it will calculate accordingly. I suggest you make the use of the round when making the calculating. 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



aj1973
Community Champion
Community Champion

Hi @RGallagher320 

Use ROUND function to your measure

ROUND(Insert your measure here,0)

 

Regards
Amine Jerbi
https://www.linkedin.com/in/amine-jerbi-2b3a8928/

Did I answer your question? Mark my post as a solution!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.