Average of percentages

[wonkymeister]

Morning,

i'm trying to understand how to calculate the average of a expression of percentages:

Training	Passed	Failed	Passed %
Fire Safety	200	48	81%
Data Protection	150	30	83%
First Aid	100	50	66%
Health & Safety	150	25	86%

below this pivot table i'd like to display the average % passed: Average % Passed Result = 79%

Passed expression :

count({$<mandatory_or_optional = {'M'},passed = {'1'}>}1)

Failed expression:

count({$<mandatory_or_optional = {'M'},passed = {'0'}>}1)

Passed % expression:

count({$<mandatory_or_optional = {'M'},passed = {'1'}>}1)/count({$<mandatory_or_optional = {'M'},passed = {'1','0'}>}1)

to get the average % do i have to wrap the % expression with Aggr using the Total function? I'm a bit lost with this!

cheers guys

[tresesco]

If you want to show it in a text box, yes, you can try using aggr() and 'total'. Please let know (and perhaps share a sample qvw) if that doesn't work .

[sunny_talwar · Accepted solution]

You should be able to use this and enable partial sum for your training dimension

Avg(Aggr(count({$<mandatory_or_optional = {'M'},passed = {'1'}>}1)/count({$<mandatory_or_optional = {'M'},passed = {'1','0'}>}1), Training))

[swuehl]

Depends on how you define your average.

It may be enough to just use

=count({$<mandatory_or_optional = {'M'},passed = {'1'}>}1)

/ count({$<mandatory_or_optional = {'M'},passed = {'1','0'}>}1)

i.e. the same expression for the grouped trainings and the total. This will consider the different number of exams in each training, while using the aggr() function, you ignore the different number of exams and just calculate the numeric average of the four percentages.

edit: Guess you are already decided by defining the expected result of 79%...

[wonkymeister]

Sunny, perfect, works a treat, thank you!