I think that this will do the first part
dat1 <- structure(list(week = c(1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L,
2L, 2L), col1 = c("a", "a", "a", "b", "b", "b", "a", "a", "a",
"b", "b", "b"), col2 = c("lbl1", "lbl2", "lbl3", "lbl4", "lbl5",
"lbl6", "lbl1", "lbl2", "lbl3", "lbl4", "lbl5", "lbl6"), val1 = c(9304L,
9616L, 9573L, 9606L, 9270L, 9657L, 9281L, 9634L, 9801L, 9718L,
9588L, 9619L), val2 = c(9542L, 9734L, 9684L, 9611L, 9492L, 9850L,
9787L, 9690L, 9966L, 9786L, 9669L, 9739L), kpi = c(0.97505764,
0.987877543, 0.988537794, 0.999479763, 0.976611884, 0.980406091,
0.948298764, 0.994220846, 0.983443709, 0.993051298, 0.991622712,
0.987678406)), class = "data.frame", row.names = c(NA, -12L))
library(tidyverse)
dat1 %>% mutate( kpi = val1 / val2) %>%
group_by(week, col1) %>%
summarise(sum1 = sum(val1), sum2 = sum(val2), kp1 = sum(kpi))
but I am not clear on the second part.
Do you want to exclude the lowest value in a week By col1 subset in the raw data and tend do the summing?