5 Replies Latest reply on May 1, 2015 2:37 PM by Paul Edwards

# Caclulated field based on calculated field

I'm struggling to find a solution for a calculated field that relies on another calculated field. Namely I made a calculated field that divides the sum of two fields which gives me an average number. I want to take that value and divide it by the average of another field. In the image below agg(Avg minutes watched) is sum(duration)/sum(starts) what I want is Avg Minutes watched / Avg Length but I can not come up with a formula that produces the results I want.

I attached a workbook New field
 1.42/3 = 47%
 1.42/4 = 36%
 37%
 40%
 33%
 35%

Thanks

• ###### 1. Re: Caclulated field based on calculated field

Hi Paul,

Try [Avg Minutes Watched]/avg([length]) in a new calculated field.

-Fred

• ###### 2. Re: Caclulated field based on calculated field

I was able to create a new calc (Calculation1) that is "[Avg Minutes Watched] / [Avg Length]".  At first it showed 0, but then I right clicked and changed the format to be %

• ###### 3. Re: Caclulated field based on calculated field

I tried that prior but the percentage value I get is different than it should be. In the first row 1.42/3 should be 47.3% not 45.5%. My assumption is it has something to do with how the aggregation is working but I'm not sure if there is anyway around it.

• ###### 4. Re: Caclulated field based on calculated field

Hi Paul, the reason its 45.5 instead of 47.3 is because average length isn't exactly an integer, there are decimals. (1.42/3.13 = .45)  If you want 47.3, then do [Avg Minutes Watched]/round(avg([length])). -Fred

• ###### 5. Re: Caclulated field based on calculated field

Ah yes that makes sense, I'm so used to seeing it as an integer that I forgot it's not.