2 Replies Latest reply on Dec 7, 2016 2:45 PM by Ziaul Huq

# Rounding by Multiples

Hello,

I have a really simple percentage of total calculation but the end user would like the final value to be in multiple of 5.  So if a value is 22.08%, they would like this to show as 20.00% and if a value is 17.79%, then this should be displayed as 20.00%.  I searched the forum and saw a few example of using Ceiling and Floor functions but am not having any luck in getting it to work.

Would someone please let me know if it is possible to accomplish what I am trying to do?  Attached is a workbook based on the Sample Superstore data.

Thank you!

Z

• ###### 1. Re: Rounding by Multiples

Hi Ziaul,

I was able to round to the nearest 5% by using the following formula in a calculated field:

CASE RIGHT(STR(ROUND( (SUM([Sales]) / TOTAL(SUM([Sales]))), 2)), 1)

WHEN "0" THEN (ROUND( (SUM([Sales]) / TOTAL(SUM([Sales]))), 2))

WHEN "1" THEN (ROUND( (SUM([Sales]) / TOTAL(SUM([Sales]))), 2)) - .01

WHEN "2" THEN (ROUND( (SUM([Sales]) / TOTAL(SUM([Sales]))), 2)) - .02

WHEN "3" THEN (ROUND( (SUM([Sales]) / TOTAL(SUM([Sales]))), 2)) + .02

WHEN "4" THEN (ROUND( (SUM([Sales]) / TOTAL(SUM([Sales]))), 2)) + .01

WHEN "5" THEN (ROUND( (SUM([Sales]) / TOTAL(SUM([Sales]))), 2))

WHEN "6" THEN (ROUND( (SUM([Sales]) / TOTAL(SUM([Sales]))), 2)) - .01

WHEN "7" THEN (ROUND( (SUM([Sales]) / TOTAL(SUM([Sales]))), 2)) - .02

WHEN "8" THEN (ROUND( (SUM([Sales]) / TOTAL(SUM([Sales]))), 2)) + .02

WHEN "9" THEN (ROUND( (SUM([Sales]) / TOTAL(SUM([Sales]))), 2)) + .01

END

And you can replace SUM([Sales]) / TOTAL(SUM([Sales])) with another field if needed.

There may be a more elegant solution out there but I hope this helps in the meantime!

Best,
Sarah

• ###### 2. Re: Rounding by Multiples

Thank you Sarah!  I was able to implement this on my workbook.

Thanks again!