Average of average caculation

Question & Answer
,
1
week1
c b c s d f g
OTD % - offers average 0.0% 11.1% 75.0% 0.0% 65.6% 0.0% 100.0%
OTD % - partners average 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
otd_num 0 2 24 0 227 0 1
otp denom 56 18 32 3 346 17 1

Right now, i can’t get the right formula for [OTD % - partners average] , please help.

The logic for calculation is sum(otd* OTD % - offers average)/ counta( vendor alias)
ex: OTD % - partners average =0*56 +11.1%*18+75% * 32+0 * 3+65.6% * 346+ 0 * 17+ 100% *1= 36.28%

otd_num formula: distinct_count(ifelse({otd_status}=1,{Offer Published},NULL))
otp denom formula :distinct_count({Offer Published})
OTD % - offers average= otd_num/otp_donom

2

Hi @Iris_Zhou ,
did you try to break the calculation down to it parts?

  1. calculate the “otd %” otd* OTD % - offers average for each
  2. the sumOver(otd %,week)
  3. final 2. / counta( vendor alias)
    BG