Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
PS0304
Frequent Visitor

SUM of SALES not rounding to the nearest 100 before decimal point correctly

Hi Community Members,

 

I am trying to roundoff SALES column to the nearest 100 before decimal point. ROUND function is rounding SALES to the nearest 100 before the decimal point but as soon as I change SALES column to SUM of SALES, the ROUND function does not work as expected. 

PS0304_0-1697302656161.png

First column is SALES and second is round off of SALES - this is working as expected but the next images is SUM of SALES (not working as expected)

 

 

PS0304_1-1697302740116.png

First column is SALES and second column is SUM of SALES. The second column is not coming-up correctly. 

 

Can someone please share thoughts on this issue?

 

 

1 ACCEPTED SOLUTION

Hi @PS0304 
Sorry for misunderstanding.

You can use : 

sum_rounded = Round([sum_],-2)
Ritaf1983_0-1697336990910.png

I also updated PBIX which I linked in the first response.

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

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

View solution in original post

5 REPLIES 5
Ritaf1983
Super User
Super User

Hi @PS0304 
You can use measure : 

sum_rounded = FLOOR([sum_],100)
Ritaf1983_0-1697304915652.png

Link to PBIX with example 

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

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

Hi @Ritaf1983 

Thanks for responding and trying. Much appreciated.

I tried but it's rounding slightly off. Image on the left side is the actual SUM of SALES and the right image is after using the FLOOR function. The function should return 25,267,300 because 57 should be rounded off to the next hundred. Could you please provide more insight here.

PS0304_0-1697306292960.png

 

Hi @PS0304 
Sorry for misunderstanding.

You can use : 

sum_rounded = Round([sum_],-2)
Ritaf1983_0-1697336990910.png

I also updated PBIX which I linked in the first response.

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

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

@Ritaf1983 Thank you so much. It worked.

@PS0304 
You're welcome 🙂

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.