Column Actions

Method to round percentages in such a way that the total is always 100%.

In this example, the percentages (rounded to 1 decimal) are computed using ratio_to_report (colum Perc). The total of these is 100.1, which is mathematically correct, but of course not what we'd like to see.
The difference between 100.1 and the (ideal) 100 (column Perc Diff) is added to the row with the highest value (120).

In this example there are 2 rows with this highest value, so we have to force a winner.
For this I used rownum, because that allows the Descr column of rows to be identical without causing problems. A side effect is that the sorting can be a bit odd; in this case it causes the "x-120-b" row to be the winner that the difference is added to (and therefore the top row after sorting), which may not be what you want. If the Descr column is always a unique value in your case, you can change the "perc_count" line in "a" to this: count(*) over (order by t.perc desc, t.descr desc) as perc_count

Region

Identification
	Sequence	20
	Title	IR
	Type	Interactive Report
Source
	Source Type	DYNAMIC_QUERY
	Region Source	with tx as ( select 'x-60' as descr, 60 as value from dual union all select 'x-120-a' as descr, 120 as value from dual union all select 'x-120-b' as descr, 120 as value from dual union all select 'x-23' as descr, 23 as value from dual ) , t as ( select tx.descr , tx.value , round( 100 * ratio_to_report( tx.value ) over (), 1 ) as perc , rownum as row_num -- needed for forcing a "single highest value" when sorting rows with identical values from tx ) , a as ( select t.descr , t.value , t.perc , t.row_num , 100 - sum(t.perc) over () as perc_diff -- the difference between 100% and the sum of all percentages , count(*) over (order by t.perc desc, rownum) as perc_count from t ) select a.* , a.perc + ( case when a.perc_count = 1 then perc_diff else 0 end ) as perc_fixed -- add perc_diff to the "single highest" percentage from a order by perc_fixed desc , a.descr