SSRS percentage rounding problem in an expression

Abdul Khan
Abdul Khan used Ask the Experts™
on
Hello All,

I am using the following expression to calculate percentage in SSRS,
=Format(Fields!PCTanswers.Value/100,"0%")
however, sometime the calculated values aren't equal to 100%  they are 99, so the rounding is not working correctly.
Can someone please help?  Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
Commented:
use =ROUND rather than format
=ROUND(Fields!PCTanswers.Value/100,0)

Format only truncates
Using rounding is correct, as David suggested.

However, if you have several values that should add up to 100%, and each value is rounded, you may still end up with a sum that is different from 100%.  That is unavoidable when you are rounding values.

Say for example that you have three values that each represent a third of the total, or 33.333333333333%. When you round them, each will be rounded down to 33%, and the total is 99%.

If you want the values to add up to 100%, you can use the largest reminder method:

1. Round all the values down (but keep the original values also, for now).
2. Calculate how much you have from the sum of the rounded values up to 100%.
3. Distribute that difference by adding one to values, in descending order of the decimal part.

Ref: Wikipedia: Largest remainder method

Author

Commented:
Thanks so much for your assistance!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial