Calculated field based on sum and count of other field

I have a dataset like this:

Segment Organisation name nr of visitors
A {name} 3
A {name} 7
B {name} 20
B {name} 20
B {name} 10
B {name} 10
C {name} 8
C {name} 8
C {name} 8
C {name} 6

I have a pivot table with three columns, where values are displayed as percentage:

Segment Count of organisations Sum of visitors
A 20% 10%
B 40% 60%
C 40% 30%

I want to add a third column which does sum of visitors% divided by count of org.%.

Segment Count of organisations Sum of visitors Leverage
A 20% 10% 0.5
B 40% 60% 1.5
C 40% 30% 0.75

Using a calculated field I can't seem to do this. Is it possible to have this as a field in the pivot table?

1 Answer

Although this is a workaround, you can add another column with the below formula and add that column in pivot table as Summarize values by Average.

Formula: =(SUMIF([Segment],[@Segment],[nr of visitors])/SUM([nr of visitors]))/(COUNTIF([Segment],[@Segment])/COUNTA([Organisation name]))

Screenshot below:

3

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy