is there an elegant way to ensure percentages with 2 outcomes add up to 100% after decimals are removed?

Question

Hi, we have raw data for counts of items within various groupings, counts of how many items errored in each grouping and counts of how many items succeeded in each grouping. The sum of errored + sum succeeded always = that groupings count of items.

i simulated to four decimal places (i didnt think more decimal places would tell me anything different) all combos of 2 outcome percentages to see if the rounding function (applied separately to each) could guarantee both percentages for each group would sum to 100% after fractions of percentages were removed. You can see the script below. There are 101 cases between .0000 and 1.0000 when incrementing / decrementing by .0001 where rounding doesn't guarantee such a thing.

i kept the rounding in but applied a final tweak you can see in the partial selection list under the script. This tweak applies to cases where the 2 percentages do not total 100%. The tweak for these 101 cases is to set the second pct to 100 - the first pct.

if there is a more elegant / balanced way to do this, it would be easier for me to explain to my user.

declare @pct1 decimal (5,4)=.0000
declare @pct2 decimal (5,4)

declare @result decimal(5,4)

while @pct1 < 1.0

begin 
  set @pct2 = 1.0-@pct1
  set @result = round(@pct1,2) + round(@pct2,2)
  if 100 * @result <> 100.0 select 'an issue ' , @pct1,@pct2,round(@pct1,2) rounded1,round(@pct2,2) rounded2,
     100*round(@pct1,2) dec1removed,
     100*round(@pct2,2) dec2removed,
  100-100*round(@pct1,2) recalcddec2removed
  set @pct1 = @pct1 + .0001
end

select 'finished'


.............PctErrored, -- i dont believe these will ever exceed 100 with the rounding choice being used
          case when (PctErrored is not null) and 
                    (PctCompleted is not null) and 
                    (PctErrored + PctCompleted <> 100.0)
                then 100.0 - PctErrored
                     else PctCompleted end PctCompleted

 

Answer 1

If you were dealing with money you would have to do this. (And I believe that I have written code for this at some point.)

For percentages, you would be distorting the value you apply it to. It is better to show data as-is and add a comment that rounding causes the sum to be <> 100.

Answer 2

Not really.

You are losing precision by rounding. This causes the possibility of it adding up to 100+-X depending on how many values you are rounding.

Answer 3

Hi @db042190
Maybe we can hold a running (non-integer) position and rounds the value, then uses that with history to determine what value should be used.
Here is an sample ,please have a check:

create table #test([percent] float )  
insert into #test values(13.736332),(47.689636),(9.686008),(28.888024)  
;WITH CTE AS  
(  
 SELECT [percent],SUM([percent])OVER(ORDER BY [percent] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)AS sum_percent,  
        ROUND(SUM([percent])OVER(ORDER BY [percent] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),2) AS round_sum_percent   
 FROM #test  
)  
SELECT [percent],sum_percent,round_sum_percent,LAG(round_sum_percent,1,0)OVER(ORDER BY [percent])AS prev_sum_percent,  
       round_sum_percent-LAG(round_sum_percent,1,0)OVER(ORDER BY [percent])AS what_we_need  
FROM CTE  

In this way, you don't round the percentage itself. Instead, you round up the cumulative value and calculate the best integer to reach that value from the previous baseline - which is the cumulative value (rounded up) from the previous row.
This works because you don't lose information at each stage, but use it more intelligently.
The "correct" rounded values are in the last column and you can see that they sum to 100.

Output:

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.