
1. Re: Rounding by Multiples
Sarah Ebreo Dec 7, 2016 11:08 AM (in response to Ziaul Huq)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
Rounding Example (1).twbx 442.8 KB


2. Re: Rounding by Multiples
Ziaul Huq Dec 7, 2016 2:45 PM (in response to Sarah Ebreo)Thank you Sarah! I was able to implement this on my workbook.
Thanks again!