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

    Rounding by Multiples

    Ziaul Huq

      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
          Sarah Ebreo

          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
            Ziaul Huq

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

             

            Thanks again!