Get percent complete in Gantt chart

Lester_Tai

I need to get percent complete in Gantt chart per below request.

Filter column `Region_TWC` to get each filtering. ‘NAM’ , ‘EUROPE’ , ‘PACIFIC’ , ‘JAPAN’ , ‘KOREA’ , ‘GCA’ , ‘SEA’.

Then filter column `Meet FOB DL_AR`, to get the rows which is equal to 'Meet AR DL'

After above filtering, to get each region’s total `Ordered unit` in column `Ordered unit`.

Comparing each region’s total `Ordered unit’ to SUM(distinct(`PAR in PQ`)) in column `PAR in PQ`

If total `Ordered unit` is equal or more than SUM(distinct(`PAR in PQ`)), return SUM(distinct(`PAR in PQ`)) else return total `Ordered unit`.

Use the return number to be divided by SUM(distinct(`PAR in PQ`)) to get percent complete in Gantt chart.

I created below calculated field to get each region subtotal number.

CASE

WHEN SUM(CASE WHEN `Region_TWC` IN ('NAM','EUROPE','PACIFIC','JAPAN','KOREA','GCA','SEA')

AND `PO Type` IN ('Sample','Demo','SF','M0','M1') AND `Meet FOB DL_AR`= 'Meet AR DL' THEN `Ordered unit` END)

>= SUM(DISTINCT `PAR in PQ`)

THEN SUM(DISTINCT `PAR in PQ`)

WHEN SUM(CASE WHEN `Region_TWC` IN ('NAM','EUROPE','PACIFIC','JAPAN','KOREA','GCA','SEA')

AND `PO Type` IN ('Sample','Demo','SF','M0','M1') AND `Meet FOB DL_AR`= 'Meet AR DL' THEN `Ordered unit` END)

< SUM(DISTINCT `PAR in PQ`)

THEN SUM(CASE

WHEN `Region_TWC` IN ('NAM','EUROPE','PACIFIC','JAPAN','KOREA','GCA','SEA')

AND `PO Type` IN ('Sample','Demo','SF','M0','M1')

AND `Meet FOB DL_AR`= 'Meet AR DL' THEN `Ordered unit`END)

END

However, when I generate Sum of each region subtotal number, I found it is not aligned to total number. Total number 38k vs. 34k if just sum of each region’s number.